Delete macro for blank rows/column on selected tabs

Assume that there are two tabs with data on a file and each tab has a different number of rows and columns populated. What I need to do is to delete all the rest of the blank rows and columns from those tabs via a macro run.  Can I get a working sample for that please?  Thank you in advance.
trusxlsolBusiness Systems AnalystAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

byundtMechanical EngineerCommented:
You might try a macro like this to delete your blank rows and columns. It works on the active worksheet.
Sub DeleteEmptyRowsAndColumns()
Dim rg As Range
Dim i As Long, j As Long
Application.ScreenUpdating = False
Set rg = ActiveSheet.UsedRange
For i = rg.Rows.Count To 1 Step -1
    If Application.CountA(rg.Rows(i)) = 0 Then rg.Rows(i).Delete
For j = rg.Columns.Count To 1 Step -1
    If Application.CountA(rg.Columns(j)) = 0 Then rg.Columns(j).Delete
End Sub

Open in new window


This macro should delelte all blank rows 'below' and colmns 'after to the right' of your existing data in all of the worksheets in your current workbook.

I have put in the attached workbook some data for trial. Simply make sure your macros are activated and choose to run the macro called DeleteBlankRowsCols

Sub DeleteBlankRowsCols()
Dim WS As Worksheet
Dim MaxRow As Long, MaxCol As Long
Dim Rng As Range, RngtoDel As Range

For Each WS In ActiveWorkbook.Worksheets
    '---> Get LastRow and LastCol for that sheet
    Set Rng = WS.UsedRange
    MaxRow = Rng.Rows.Count
    MaxCol = Rng.Columns.Count
    '---> Delete All Columns after the usedrange
    Set RngtoDel = WS.Range(WS.Cells(1, MaxCol + 1), WS.Cells(1, WS.Columns.Count))
    '---> Delete All Rows after the usedrange
    Set RngtoDel = WS.Range(WS.Cells(MaxRow + 1, 1), WS.Cells(WS.Rows.Count, 1))

Next WS

MsgBox ("All blank Rows and Columns Successfully deleted in all sheets of this workbook.")

End Sub

Open in new window


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
trusxlsolBusiness Systems AnalystAuthor Commented:
Thank you!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Applications

From novice to tech pro — start learning today.