?
Solved

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

Posted on 2015-01-15
11
Medium Priority
?
112 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
11 Comments
 
LVL 52

Accepted Solution

by:
Rgonzo1971 earned 1000 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 52

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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 4

Assisted Solution

by:Monika Bharti
Monika Bharti earned 1000 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 52

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 52

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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

770 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