Vba to delete blank rows only

[decadence]

Hi, I need a macro that can delete all empty rows in a worksheet, but not the empty cells that are in a row with data. I have tried but the closest I have got is the rows being deleted 1 at a time from bottom up on a loop and my pc froze. Can someone help me please.

 

[jtakw]

Hi, I need a macro that can delete all empty rows in a worksheet, but not the empty cells that are in a row with data. I have tried but the closest I have got is the rows being deleted 1 at a time from bottom up on a loop and my pc froze. Can someone help me please.

Hi,

Don't know what loop code you're using, but looping may be the only way...Try:

Sub RemoveEmptyRows()
Dim sh As Worksheet
Dim lr As Long, i As Long
    Set sh = ActiveSheet
    With Application
        .ScreenUpdating = False
        .DisplayStatusBar = False
        .Calculation = xlCalculationManual
        .EnableEvents = False
                
            lr = sh.Cells(Rows.Count, 1).End(xlUp).Row
            For i = lr To 1 Step -1
                If WorksheetFunction.CountA(Rows(i)) = 0 Then
                    Rows(i).EntireRow.Delete
                End If
            Next i
            
        .EnableEvents = True
        .Calculation = xlCalculationAutomatic
        .DisplayStatusBar = True
        .ScreenUpdating = True
    End With
End Sub

Please note the above code uses Column A to find the last used row, if your last row with data is not in Column A, please adjust Column reference accordingly.
Let us know if this works out for you.

 

[JackDanIce]

Another suggestion:

Sub RemoveER()

    Dim rng As Range
    Dim x   As Long
    
    Application.ScreenUpdating = False
    
    Set rng = cell(rows.count, 1).End(xlUp).Offset(1)
    
    For x = Cells(rows.count, 1).End(xlUp).row To 2 Step -1
        If Cells(x, Columns.count).End(xlToLeft).Column = 1 And LenB(Cells(x, 1).value) = 0 Then
            Set rng = Union(Cells(x, 1), rng)
        End If
    Next x
    
    rng.EntireRow.Delete
    Set rng = Nothing
    
    Application.ScreenUpdating = True
        
End Sub

 

[Peter_SSs]

.. and a non-looping way to test in a copy of your workbook.

Sub Del_Rws()
  Dim lc As Long
  
  Application.ScreenUpdating = False
  With ActiveSheet.UsedRange
    lc = .Columns.Count + 1
    With .Resize(, lc)
      With .Columns(lc)
        .FormulaR1C1 = "=IF(COUNTA(RC1:RC[-1]),"""",1)"
        .Value = .Value
      End With
      .Sort Key1:=.Columns(lc), Order1:=xlAscending, Header:=xlNo, _
          OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
    End With
    On Error Resume Next
    .Columns(lc).SpecialCells(xlConstants, xlNumbers).EntireRow.Delete
    On Error GoTo 0
  End With
  Application.ScreenUpdating = True
End Sub

 

[decadence]

Hi, the first code doesn't work. the second code errors with sub or function, the third kind of works. but when I copy over all columns into notepad there is a lot of space added at the end of the data because I used a copy and paste values up to row 10,000 in my other macros, so the rows seem to delete on excel but not properly once copied over to notepad. if you could tweak the code so it deletes empty rows up to the last cell with data in it, that would be a massive help thanks.

 

[JackDanIce]

Had a typo, try:

Sub RemoveER()

    Dim rng As Range
    Dim x   As Long
    
    Application.ScreenUpdating = False
    
    Set rng = Cells(rows.count, 1).End(xlUp).Offset(1)
    
    For x = Cells(rows.count, 1).End(xlUp).row To 2 Step -1
        If Cells(x, Columns.count).End(xlToLeft).Column = 1 And LenB(Cells(x, 1).value) = 0 Then
            Set rng = Union(Cells(x, 1), rng)
        End If
    Next x
    
    rng.EntireRow.Delete
    Set rng = Nothing
    
    Application.ScreenUpdating = True
        
End Sub

 

[decadence]

Nothing happens with this code. I basically have to delete the rows of duplicates out of column A, then delete the empty rows that were occupied by them, nothing happens when deleting them and the last row of data is a blank cell which is where the data finished before the duplicates were removed.

This is the remove duplicates code I'm using:

Sub RemoveDuplicates()
'
' RemoveDuplicates Macro
' Remove Duplicates Rows
'
Columns("A:A").Select
Dim rng As Range
Dim x As Integer

'Optimize code execution speed
Application.ScreenUpdating = False

'Determine range to look at from user's selection
On Error GoTo InvalidSelection
Set rng = Selection
On Error GoTo 0

'Ask user which column to look at when analyzing duplicates
On Error GoTo InputCancel
x = InputBox("Which column should I look at? (Number only!)", _
"Select A Column", 1)
On Error GoTo 0

'Optimize code execution speed
Application.Calculation = xlCalculationManual

'Remove entire row if duplicate is found
rng.EntireRow.RemoveDuplicates Columns:=x

'Change calculation setting to Automatic
Application.Calculation = xlCalculationAutomatic

Exit Sub

'ERROR HANDLING
InvalidSelection:
MsgBox "You selection is not valid", vbInformation
Exit Sub

InputCancel:

End Sub

 

[JackDanIce]

This code will remove the entire row of a duplicate (not original), if found in column A. Try this macro independently with your original data set, do not try to use after a different macro:

Sub RemoveDuplicateRows()

    Dim x   As Long
    Dim LR  As Long
    Dim dic As Object
    
    Set dic = CreateObject("Scripting.Dictionary")
    
    Application.ScreenUpdating = False
        
    LR = Range("A" & rows.count).End(xlUp).row
    For x = 1 To LR
        If dic.exists(Cells(x, 1).value) Then
            Cells(x, 1).ClearContents
        Else
            dic(Cells(x, 1).value) = 1
        End If
    Next x
    
    Range("A1:A" & LR).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
    
    Application.ScreenUpdating = True
    
    Set dic = Nothing
    
End Sub

 

[Peter_SSs]

... but not properly once copied over to notepad.

Perhaps you had better tell us more about how you are doing that 'copy over to notepad'

 

[decadence]

Really? Ok so i highlight the data on my sheet and either right click then click copy or I hold Ctrl and press C, then i open up a new notepad and I either Right click then paste it I hold Ctrl and press v to paste my data into the notepad then I save the notepad as what i need it to be. Not being funny but Im not doing anything stupid like using a calculator to open windows media. Plain and simply There are spaces at the end of my data in notepad although i have tried using various macros in excel to delete all rows below the data,to remove the spaces when i copy it into notepad so the notepad file can be loaded into other software. Does this clarify why I'm after a macro to delete rows?