+ Reply to Thread
Results 1 to 7 of 7

Deleting Content of a Cell till the End of Right (Not Row)

Hybrid View

  1. #1
    Registered User
    Join Date
    11-08-2009
    Location
    Portugal
    MS-Off Ver
    Excel 2007
    Posts
    6

    Deleting Content of a Cell till the End of Right (Not Row)

    Dear All,

    I need help on Excel 2007 Macro , the below coding searches for a string "recvtiming" in a cell as a wildcard and delete it entire row once it founds it in a single active sheet.

    Question,
    1. How do I manipulate it to search for the same string and upon finding
    a) Delete its cell content and all the contents from its right hand of side till its last column which have a value from the same row on all 50 sheets in the workbook.

    e.g
    a b c d recv 0 1 2 3 4

    It should return,
    a b c d

    ====================Start Code ====================
    
    With ActiveSheet
        .Select
    
        ViewMode = ActiveWindow.View
        ActiveWindow.View = xlNormalView
        .DisplayPageBreaks = False
    
        Firstrow = .UsedRange.Cells(1).Row
        Lastrow = .UsedRange.Rows(.UsedRange.Rows.Count).Row
            
        'Loop from Firstrow to Lastrow
        For Lrow = Firstrow To Lastrow
            
        Firstcol = .UsedRange.Cells(1).Column
        Lastcol = .UsedRange.Columns(.UsedRange.Columns.Count).Column
        Currentcell = ActiveCell.Address.Column
            
                If Application.CountIf(.Rows(Lrow), "recv*") > 0 Then Range(Cells(Lrow, Currentcell), Cells(Lrow, Lastcol)).ClearContents
                
        Next Lrow
    
    End With
    ====================End Code ====================
    Last edited by svanan; 11-10-2009 at 07:21 PM. Reason: Conforming to the guideline

  2. #2
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,259

    Re: Deleting Content of a Cell till the End of Right (Not Row)

    Hello svanan,

    Welcome to the Forum!

    This macro will look through all the worksheets and find all occurances of "revtiming" and delete the data in that cell and all others to the right in the same row.
    Sub DeleteColumns()
    
      Dim FirstAddx As String
      Dim FoundIt As Range
      Dim LastCol As Long
      Dim What As String
      Dim Wks As Worksheet
      
        What = "recvtiming"
        
        For Each Wks In Worksheets
          LastCol = Wks.UsedRange.Columns.Count
          Set FoundIt = Wks.UsedRange.Find(What, , xlValues, xlWhole, xlByColumns, xlNext, False)
          If Not FoundIt Is Nothing Then
             FirstAddx = FoundIt.Address
               Do
                 FoundIt(1, LastCol - FoundIt.Column).ClearContents
                 Set FoundIt = Wks.UsedRange.FindNext(FoundIt)
               Loop While Not FoundIt Is Nothing And FoundIt.Address <> FirstAddx
          End If
       Next Wks
       
    End Sub
    Sincerely,
    Leith Ross

    Remember To Do the Following....

    1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.
    2. Thank those who have helped you by clicking the Star below the post.
    3. Please mark your post [SOLVED] if it has been answered satisfactorily.


    Old Scottish Proverb...
    Luathaid gu deanamh maille! (Rushing causes delays!)

  3. #3
    Registered User
    Join Date
    11-08-2009
    Location
    Portugal
    MS-Off Ver
    Excel 2007
    Posts
    6

    Question Re: Deleting Content of a Cell till the End of Right (Not Row)

    Thanks Mate for the advice.

    I tried testing the codes but somewhere somehow it is not running. Will try to trouble shoot it.Perhaps its due to the last columns of each row might not necessarily be the same length.

    Attaching my test macro file in case you like to see to check it out.
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    11-08-2009
    Location
    Portugal
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: Deleting Content of a Cell till the End of Right (Not Row)

    Still unable to get it right. Any help would be good.

  5. #5
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2010, 2019
    Posts
    40,689

    Re: Deleting Content of a Cell till the End of Right (Not Row)

    Try this:
    Sub DeleteColumns()
        Dim rFind       As Range
        Dim wks         As Worksheet
    
        For Each wks In ActiveWorkbook.Worksheets
            With wks
                Set rFind = .UsedRange.Find(What:="recvtiming", _
                                            LookIn:=xlValues, _
                                            LookAt:=xlWhole, _
                                            MatchCase:=False)
                Do Until rFind Is Nothing
                    .Range(rFind, .Cells(rFind.Row, .Columns.Count)).ClearContents
                    Set rFind = .UsedRange.FindNext(rFind)
                Loop
            End With
        Next wks
    End Sub
    Entia non sunt multiplicanda sine necessitate

  6. #6
    Registered User
    Join Date
    11-08-2009
    Location
    Portugal
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: Deleting Content of a Cell till the End of Right (Not Row)

    SHG,

    It worked perfectly.

    Could you advice where it failed earlier.Its for my own learning.

    I will find way to close this thread as solved.

    rgds
    Saravanan K

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1