Macro to remove blank rows AND rows that behind with two spaces

[jwright8]

Hello everyone,

Had an old account but had to recreate.

I've been trying to figure out a macro to remove blank rows, as well as rows that begin with two spaces in the first column (A). Every time I try something it tells me that the delete method is not supported... any pointing in the right direction (especially in regards to the second criteria I have) would be a huge help!

Thank you!

 

[AlphaFrog]

Try something like this. First test it on a copy of your data.

    [color=darkblue]Dim[/color] i [color=darkblue]As[/color] [color=darkblue]Long[/color], LastRow [color=darkblue]As[/color] [color=darkblue]Long[/color]
    
    LastRow = Cells.Find("*", , , , 1, 2).Row
    
    Application.ScreenUpdating = [color=darkblue]False[/color]
    [color=darkblue]For[/color] i = LastRow [color=darkblue]To[/color] 1 [color=darkblue]Step[/color] -1
        [color=darkblue]If[/color] Application.CountA(Rows(i)) = 0 [color=darkblue]Or[/color] Range("A" & i).Value [color=darkblue]Like[/color] "  *" [color=darkblue]Then[/color] Rows(i).Delete
    [color=darkblue]Next[/color] i
    Application.ScreenUpdating = [color=darkblue]True[/color]

 

[jwright8]

Try something like this. First test it on a copy of your data.

    [COLOR=darkblue]Dim[/COLOR] i [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR], LastRow [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
    
    LastRow = Cells.Find("*", , , , 1, 2).Row
    
    Application.ScreenUpdating = [COLOR=darkblue]False[/COLOR]
    [COLOR=darkblue]For[/COLOR] i = LastRow [COLOR=darkblue]To[/COLOR] 1 [COLOR=darkblue]Step[/COLOR] -1
        [COLOR=darkblue]If[/COLOR] Application.CountA(Rows(i)) = 0 [COLOR=darkblue]Or[/COLOR] Range("A" & i).Value [COLOR=darkblue]Like[/COLOR] "  *" [COLOR=darkblue]Then[/COLOR] Rows(i).Delete
    [COLOR=darkblue]Next[/COLOR] i
    Application.ScreenUpdating = [COLOR=darkblue]True[/COLOR]

Thanks for the super quick reply! If I had a button on Sheet1 for instance, could I do the following to run the actual operation on Sheet2?

Sub Rectangle1_Click
[COLOR=darkblue]Dim[/COLOR] i [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR], LastRow [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
    
    LastRow = Cells.Find("*", , , , 1, 2).Row
    
    Application.ScreenUpdating = [COLOR=darkblue]False[/COLOR]
    [COLOR=darkblue]For[/COLOR] i = LastRow [COLOR=darkblue]To[/COLOR] 1 [COLOR=darkblue]Step[/COLOR] -1
        [COLOR=darkblue]If[/COLOR] Worksheets(Sheet2).CountA(Rows(i)) = 0 [COLOR=darkblue]Or[/COLOR] Worksheets(Sheet2).Range("A" & i).Value [COLOR=darkblue]Like[/COLOR] "  *" [COLOR=darkblue]Then[/COLOR] Worksheets(Sheet2).Rows(i).Delete
    [COLOR=darkblue]Next[/COLOR] i
    Application.ScreenUpdating = [COLOR=darkblue]True
End Sub
[/COLOR]

Forgive me if that seems way off the mark, similar methods were used in the help files for referencing sheet names in VBA.

Thanks for the help so far!

 

[AlphaFrog]

You're welcome. Try this...

    [color=darkblue]Dim[/color] i [color=darkblue]As[/color] [color=darkblue]Long[/color], LastRow [color=darkblue]As[/color] [color=darkblue]Long[/color]
    
    [B][color=darkblue]With[/color] Sheets("Sheet2")[/B]
    
        LastRow = [COLOR=#ff0000][B].[/B][/COLOR]Cells.Find("*", , , , 1, 2).Row
        
        Application.ScreenUpdating = [color=darkblue]False[/color]
        [color=darkblue]For[/color] i = LastRow [color=darkblue]To[/color] 1 [color=darkblue]Step[/color] -1
            [color=darkblue]If[/color] Application.CountA([COLOR=#ff0000][B].[/B][/COLOR]Rows(i)) = 0 [color=darkblue]Or[/color] [COLOR=#ff0000][B].[/B][/COLOR]Range("A" & i).Value [color=darkblue]Like[/color] "  *" [color=darkblue]Then[/color] [COLOR=#ff0000][B].[/B][/COLOR]Rows(i).Delete
        [color=darkblue]Next[/color] i
        Application.ScreenUpdating = [color=darkblue]True[/color]
    
   [B] [color=darkblue]End[/color] [color=darkblue]With[/color]

[/B]

 

[jwright8]

Awesome! It worked perfectly.

Just for anyone else who sees this, I made a cell on Sheet1 (I1) be designated as something I could put the sheet name in (just in case I wanted to run it on another sheet in the same workbook and didn't want to edit the macro). I know it's not that different, but I figured I'd share in case it was useful to anyone else.

Sub Rectangle1_Click()


    Dim i As Long, LastRow As Long
    Dim shtName As String
    
    shtName = Range("I1")
    
    With Sheets(shtName)
    
        LastRow = .Cells.Find("*", , , , 1, 2).Row
        
        Application.ScreenUpdating = False
        For i = LastRow To 1 Step -1
            If Application.CountA(.Rows(i)) = 0 Or .Range("A" & i).Value Like "  *" Then .Rows(i).Delete
        Next i
        Application.ScreenUpdating = True
    
    End With




End Sub

Thanks again for the help!

 

[jgscanlon]

Hello!

I'm looking for a way to delete or hide any row that has a value of 0 in column C. How would I modify the code above to accomplish this? Or would entirely different code be appropriate?

The number of rows is variable each time.

Thank you!