Solved

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

Posted on 2015-01-15
11
109 Views
Last Modified: 2015-01-15
Hello,

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

thanks.
0
Comment
Question by:Flora
11 Comments
 
LVL 49

Accepted Solution

by:
Rgonzo1971 earned 250 total points
ID: 40550917
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
 
LVL 6

Author Comment

by:Flora
ID: 40550923
Did not work,  please see attached file.
Book1.xlsm
0
 
LVL 49

Expert Comment

by:Rgonzo1971
ID: 40550926
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
Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

 
LVL 4

Assisted Solution

by:Monika Bharti
Monika Bharti earned 250 total points
ID: 40550939
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
 
LVL 49

Expert Comment

by:Rgonzo1971
ID: 40550940
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
 
LVL 2

Expert Comment

by:Pratik Makwana
ID: 40550941
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
 
LVL 6

Author Comment

by:Flora
ID: 40551001
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
 
LVL 6

Author Comment

by:Flora
ID: 40551003
Rgonzo1971
can you please give me the modified correct version of code?
0
 
LVL 6

Author Comment

by:Flora
ID: 40551004
as soon as i get reply from Rgonzo1971  i will  close the question.
0
 
LVL 49

Expert Comment

by:Rgonzo1971
ID: 40551018
Sorry not at home

If .Columns(Idx).Cells.Count = WorksheetFunction.CountBlank(.Columns(Idx)) Then
0
 
LVL 6

Author Closing Comment

by:Flora
ID: 40551056
Thank you Rgonzo1971 & Monica
0

Featured Post

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

786 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question