Delete rows with no data

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have an excel spreadsheet that I would like it to automatically look at
columns of data and if there is no data or formulas for that column of data
to go ahead and delete the column or if I have it in a row to delete the row.
Is there an easy way to do this?
 
Mindie

This would require VBA macro to be "automatic"

Sub DeleteEmptyRows()
Dim LastRow As Long
''only if entire row is blank
LastRow = ActiveSheet.UsedRange.Row - 1 + _
ActiveSheet.UsedRange.Rows.Count
Application.ScreenUpdating = False
For r = LastRow To 1 Step -1
If Application.CountA(Rows(r)) = 0 Then Rows(r).Delete
Next r
End Sub

Sub DeleteEmptycolumns()
Dim Lastcolumn As Long
''only if entire column is blank
Lastcolumn = ActiveSheet.UsedRange.Column - 1 + _
ActiveSheet.UsedRange.Columns.Count
Application.ScreenUpdating = False
For r = Lastcolumn To 1 Step -1
If Application.CountA(Columns(r)) = 0 Then Columns(r).Delete
Next r
End Sub


Gord Dibben Excel MVP
 
Back
Top