VBA to delete entire rows and columns that are empty in the used range

Hello,

I need a code that could delete all empty rows and columns in used range.

thanks.
LVL 6
FloraAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Rgonzo1971Connect With a Mentor Commented:
Hi,

pls try

Sub macro1()
For Each Cl In ActiveSheet.UsedRange.Columns
    If Cl.Address = Cl.SpecialCells(xlCellTypeBlanks).Address Then
        Cl.Delete
    End If
Next
For Each Rw In ActiveSheet.UsedRange.Rows
    If Rw.Address = Rw.SpecialCells(xlCellTypeBlanks).Address Then
        Rw.Delete
    End If
Next

End Sub

Open in new window

Regards
0
 
FloraAuthor Commented:
Did not work,  please see attached file.
Book1.xlsm
0
 
Rgonzo1971Commented:
corrected code
Sub DeleteEmtyColRow()
With ActiveSheet.UsedRange
    For Idx = .Columns.Count To 1 Step -1
        If .Columns(Idx).Cell.Count = WorksheetFunction.CountBlank(.Colums(Idx)) Then
            .Columns(Idx).Delete
        End If
    Next
    For Idx = .Rows.Count To 1 Step -1
        If .Rows(Idx).Cells.Count = WorksheetFunction.CountBlank(.Rows(Idx)) Then
            .Rows(Idx).Delete
        End If
    Next
End With
End Sub

Open in new window

EDITED Now I got it remembered delete from the end to the beginning
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
Monika BhartiConnect With a Mentor Sr. AnalyticsCommented:
Try this code to delete all empty rows and column

Sub DeleteEmptyRowsAndColumns() 
    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 
     
    LastColumn = ActiveSheet.UsedRange.Column - 1 + _ 
    ActiveSheet.UsedRange.Columns.Count 
    Application.ScreenUpdating = False 
    For c = LastColumn To 1 Step -1 
        If Application.CountA(Columns(c)) = 0 Then Columns(c).Delete 
    Next c 
End Sub 

Open in new window

0
 
Rgonzo1971Commented:
New version

Sub DeleteEmtyColRow()
With ActiveSheet.UsedRange
    For Idx = .Columns.Count To 1 Step -1
        If .Columns(Idx).Cells.Count = WorksheetFunction.CountBlank(.Colums(Idx)) Then
            .Columns(Idx).Delete
        End If
    Next
    For Idx = .Rows.Count To 1 Step -1
        If .Rows(Idx).Cells.Count = WorksheetFunction.CountBlank(.Rows(Idx)) Then
            .Rows(Idx).Delete
        End If
    Next
End With
End Sub

Open in new window

0
 
Pratik MakwanaData AnalystCommented:
Below Code delete blanks rows from given range...
Sub DeleteEmptyRows()
On Error Resume Next
    With Range("A1:C100")
    .Value = .Value
    .SpecialCells(xlCellTypeBlanks).EntireRow.Delete
    End With
End Sub

Open in new window

0
 
FloraAuthor Commented:
Thanks Rgonzo1971,

you code gives error run time error 438 Object does not support this property or method

debugger stops at
If .Columns(Idx).Cells.Count = WorksheetFunction.CountBlank(.Colums(Idx)) Then

Open in new window



@Monika
your code worked. thanks.

@Pratick,
your code was of no use. it was not based on used range and it did not work either.
0
 
FloraAuthor Commented:
Rgonzo1971
can you please give me the modified correct version of code?
0
 
FloraAuthor Commented:
as soon as i get reply from Rgonzo1971  i will  close the question.
0
 
Rgonzo1971Commented:
Sorry not at home

If .Columns(Idx).Cells.Count = WorksheetFunction.CountBlank(.Columns(Idx)) Then
0
 
FloraAuthor Commented:
Thank you Rgonzo1971 & Monica
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.