delete blank rows from other tab

[Mayte]

hi,
anybody know a coed to delte the blank rows (row is all blank) if i'm in
other sheet. let's say i'm on sheet B and want to run a macro from sheet B to
delete blank rows from sheet A, any ideas ...??

cheers,
mayte

 

[JLGWhiz]

These sites might help you to form a macro that will work for you.


http://www.cpearson.com/excel/deleting.htm#DeleteBlankRows

http://www.rondebruin.nl/delete.htm

 

[Ryan H]

You will have to put this code in a standard module. Then you can put a
command button on Sheet B and call this code. I assumed your Sheet A is
named "A", if not then you will have to change the code to the correct sheet
name.

Note: This macro may run slow if you have a huge amount of data on the
worksheet. If so we will need to make this code faster by doing a Sort first
or setting the criteria to one column.

Hope this helps! If so, let me know, click "YES" below.

Option Explicit

Sub DeleteBlankRows()

Dim lngLastRow As Long
Dim lngLastColumn As Long
Dim rw As Long
Dim col As Long

' this will help speed up the macro
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

With Sheets("A")

' get last row and column of used range
lngLastRow = .UsedRange.Rows.Count
lngLastColumn = .UsedRange.Columns.Count

' scan each row of used range
For rw = lngLastRow To 1 Step -1

' test each cell in row if empty
col = 1
Do While IsEmpty(.Cells(rw, col)) And col <= lngLastColumn
col = col + 1
Loop

' if all cells in rw are empty then delete row
If col > lngLastColumn Then .Rows(rw).Delete Shift:=xlUp
Next rw
End With

' return app settings
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub

 

[Mayte]

Brilliant !! thanks a lot mate!!

 

[Mayte]

thanks!!

 

[Ryan H]

Click "YES" (if you see it) next to "Was thispost helpful to you?" if my
reply helped. I looking to earn my Silver Medal!