Results 1 to 5 of 5

VBA - clear contents in row

This is a discussion on VBA - clear contents in row within the Excel Questions forums, part of the Question Forums category; Hi I am trying to run the loop below to find any blank cell in column Q, starting at Row ...

  1. #1
    New Member
    Join Date
    Apr 2012
    Posts
    19

    Default VBA - clear contents in row

    Hi

    I am trying to run the loop below to find any blank cell in column Q, starting at Row 10 and looping until the lastrow with contents. When there is a blank cell, I want to clear the contents of the cell from column Q to column AM. Any help would be great.

    Sub ClearEmptyRows()

    Dim xSheet As Worksheet
    Dim i As Integer

    Set xSheet = Sheets("Sector")

    xSheet.Select

    For i = 10 To xSheet.Range("Q" & 65000).End(xlUp).Row
    If xSheet.Range("Q" & i).Value = "" Then

    xSheet.Range("Q" & i & ":" & "AM" & i).Clear

    End If
    Next

    End Sub

  2. #2
    Board Regular nightcrawler23's Avatar
    Join Date
    Sep 2009
    Location
    singapore
    Posts
    720

    Default Re: VBA - clear contents in row

    What's the problem?

    Please also check if the cells are actually empty or there are blank spaces...
    ----------------------------------------------------
    Theres so much more to discover and so much more to learn.
    -----------------------------------------------------
    Instead of posting your whole problem on the forum, try writing or recording the code yourself and ask only the part that u don't understand or have errors. Try learning VBA. Its Awesome!!

  3. #3
    New Member
    Join Date
    Apr 2012
    Posts
    19

    Default Re: VBA - clear contents in row

    Nightcrawler.

    The code does not seem to be deleting the contents when there is a blank cell in column Q. If I do a normal if = "" in the worksheet it recognizes the cell as blank. It just doesn't seem to be working in VBA.

  4. #4
    New Member
    Join Date
    Apr 2012
    Posts
    19

    Default Re: VBA - clear contents in row

    I'm not sure why but this seems to have done the trick

    Private Sub ClearEmptyRows()

    Dim xSheet As Worksheet
    Dim i As Integer

    Set xSheet = Sheets("Sector")

    xSheet.Select

    For i = 10 To xSheet.Range("Q" & 65000).End(xlUp).Row
    If xSheet.Range("Q" & i).Value <> "" Then

    ' do nothing

    Else

    xSheet.Range("Q" & i & ":" & "AM" & i).Clear

    End If
    Next

    End Sub

  5. #5
    Board Regular nightcrawler23's Avatar
    Join Date
    Sep 2009
    Location
    singapore
    Posts
    720

    Default Re: VBA - clear contents in row

    Try This

    Code:
    Dim xSheet As Worksheet
    Dim i As Integer
    
    Set xSheet = Sheets("Sector")
    
    xSheet.Select
    
    For i = 10 To xSheet.Range("Q" & 65000).End(xlUp).Row
        If Trim(xSheet.Range("Q" & i)) = "" Then
            xSheet.Range("Q" & i & ":AM" & i).Clear
        End If
    Next
    
    End Sub
    ----------------------------------------------------
    Theres so much more to discover and so much more to learn.
    -----------------------------------------------------
    Instead of posting your whole problem on the forum, try writing or recording the code yourself and ask only the part that u don't understand or have errors. Try learning VBA. Its Awesome!!

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com