Delete Null/Blank Rows

T

Theresa

Hi:

I have 2 spreadsheets. The first contains all data. The second is a sheet
that I want to print. This sheet is built with an IF statement:

A10=IF('Level A Price Book'!A6="Y",'Level A Price Book'!B6,"")
B10=IF(A10<>"",VLOOKUP(A10,'Data File'!$A$2:$J$11700,2,FALSE),"")
C10=IF(A10<>"",'Level A Price Book'!F6,"")

This results in a number of rows that appear blank but still contain the
formula. I need to be able to delete the "blank" rows. I have tried a
couple of VBA scripts that I found here, but they don't work because the rows
are not "blank".

Any help would be appreciated.

Thanks,
 
D

Dave Peterson

Have you thought of applying data|filter|autofilter and showing the non-blanks?
 
J

JMB

You could try Autofilter. Select your table, turn on Autofilter
(Data/Filter/Autofilter). You should see some drop down arrows in your
header row. Click the arrow in the column you want to look in for blank
cells and select
"Blanks". This will hide rows that have non-blank cells. Select the
visible cells that are left in that column and click Edit/Delete Row. Then
turn off the Autofilter.

Be sure to practice on a copy of your data before deleting anything
permanently.
 
T

Theresa

I am very familiar with autofilter, however, this sheet is for users with
very limited excel skills so I wanted to do it automatically.
 
G

Gord Dibben

Use the Macro Recorder to record the necessary autofilter steps then run that
code from a beforeprint event in Thisworkbook.


Gord Dibben MS Excel MVP
 
T

Theresa

It doesn't work. When I use autofilter to filter for blanks, then select the
cells and delete rows, it also selects everything in between deleting the
rows which contain my data.
 
T

Theresa

Hi Gord:

I found this code which works great:

Sub Delete_blank_rows()
Dim Lrow As Long
Dim CalcMode As Long
Dim StartRow As Long
Dim EndRow As Long
With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

With ActiveSheet
.DisplayPageBreaks = False
StartRow = 11
EndRow = 500
For Lrow = EndRow To StartRow Step -1
If IsError(.Cells(Lrow, "A").Value) Then
'Do nothing, This avoid a error if there is a error in the
cell

ElseIf .Cells(Lrow, "A").Value = "" Then .Rows(Lrow).Delete

End If
Next
End With
With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With
End Sub

Can you tell me how I can get this code to run when the sheet is made active?

Thanks.
 
D

Dave Peterson

Rightclick on the worksheet tab taht should have this behavior.
Select View Code and paste this in:

Option Explicit
Private Sub Worksheet_Activate()
Dim Lrow As Long
Dim CalcMode As Long
Dim StartRow As Long
Dim EndRow As Long

With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

With Me
.DisplayPageBreaks = False
StartRow = 11
EndRow = 500
For Lrow = EndRow To StartRow Step -1
If IsError(.Cells(Lrow, "A").Value) Then
'Do nothing, This avoid a error if there is a error in the cell
ElseIf .Cells(Lrow, "A").Value = "" Then
.Rows(Lrow).Delete
End If
Next
End With
With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With
End Sub

You may want to adjust the endrow and startrow variables.
 
J

JMB

It looks like you have a working solution figured out. For future reference
regarding the autofilter approach - if you delete the data using the delete
key on the keyboard you'll delete the data in between. If you go through
Edit/Delete Rows on the menu, it will not delete the data that is hidden due
to the filter.
 
Top