[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

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

Posted on 2015-01-15
11
Medium Priority
?
113 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 53

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 53

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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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 53

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 53

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: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering 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

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
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 …
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

650 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