Solved

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

Posted on 2015-01-15
11
111 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 51

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 51

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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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 51

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 51

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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
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 …
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
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…

717 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