Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Delete Null/Blank Rows
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, |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Delete Null/Blank Rows
Have you thought of applying data|filter|autofilter and showing the non-blanks?
Theresa wrote: 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, -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Delete Null/Blank Rows
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. "Theresa" wrote: 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, |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Delete Null/Blank Rows
I am very familiar with autofilter, however, this sheet is for users with
very limited excel skills so I wanted to do it automatically. "JMB" wrote: 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. "Theresa" wrote: 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, |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Delete Null/Blank Rows
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 On Mon, 17 Dec 2007 17:34:01 -0800, Theresa wrote: I am very familiar with autofilter, however, this sheet is for users with very limited excel skills so I wanted to do it automatically. "JMB" wrote: 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. "Theresa" wrote: 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, |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Delete Null/Blank Rows
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. "Gord Dibben" wrote: 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 On Mon, 17 Dec 2007 17:34:01 -0800, Theresa wrote: I am very familiar with autofilter, however, this sheet is for users with very limited excel skills so I wanted to do it automatically. "JMB" wrote: 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. "Theresa" wrote: 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, |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Delete Null/Blank Rows
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. "Gord Dibben" wrote: 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 On Mon, 17 Dec 2007 17:34:01 -0800, Theresa wrote: I am very familiar with autofilter, however, this sheet is for users with very limited excel skills so I wanted to do it automatically. "JMB" wrote: 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. "Theresa" wrote: 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, |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Delete Null/Blank Rows
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. Theresa wrote: 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. "Gord Dibben" wrote: 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 On Mon, 17 Dec 2007 17:34:01 -0800, Theresa wrote: I am very familiar with autofilter, however, this sheet is for users with very limited excel skills so I wanted to do it automatically. "JMB" wrote: 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. "Theresa" wrote: 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, -- Dave Peterson |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Delete Null/Blank Rows
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. "Theresa" wrote: 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. "Gord Dibben" wrote: 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 On Mon, 17 Dec 2007 17:34:01 -0800, Theresa wrote: I am very familiar with autofilter, however, this sheet is for users with very limited excel skills so I wanted to do it automatically. "JMB" wrote: 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. "Theresa" wrote: 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, |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Delete Blank Rows | Excel Discussion (Misc queries) | |||
COUNTIF says Null = Blank but Blank < Null | Excel Worksheet Functions | |||
Delete all blank rows... | Excel Discussion (Misc queries) | |||
delete blank rows | Excel Discussion (Misc queries) | |||
How to delete blank rows | Excel Discussion (Misc queries) |