Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Work around to SpecialCells(xlCellTypeBlanks)...
Hey guys
I'm trying to find a work around to the following problem. I'm trying to merge the data from numerous sheets into one sheet. I can copy the data into one sheet, but am trying to eliminate blank rows. I've used the VB code below, but beacuse the rows all contain formulas, the 'SpecialCells(xlCellTypeBlanks)' doesn't evaluate these cells as blank: Code: Sheets(combinedEMParams).Range("A2:D" & Range("D" & Rows.Count).End(xlUp).Row).SpecialCells(xlCellType Blanks).EntireRow.Delete Is there a 'xlCellTypeValueBlank' or something similar? Thanks for your help Dan |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Work around to SpecialCells(xlCellTypeBlanks)...
You could try converting your formulas that don't return anything to
blanks first... Sub Macro1() Dim c As Range For Each c In ActiveSheet.UsedRange If c.Text = "" Then c.Formula = "" Next End Sub Cliff Edwards |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Work around to SpecialCells(xlCellTypeBlanks)...
Thanks for the reply mate, I could do that.
I've just thought though, If I get the VB code to check the value of a single column to base my deletion on, I could get it to work. But, how can I set the value of a cell, say A1, from a formula in cell D1? "ward376" wrote: You could try converting your formulas that don't return anything to blanks first... Sub Macro1() Dim c As Range For Each c In ActiveSheet.UsedRange If c.Text = "" Then c.Formula = "" Next End Sub Cliff Edwards |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Work around to SpecialCells(xlCellTypeBlanks)...
Hi Dan,
I'm assuming that the cells with formulas appear blank because they display "", is that correct? If so Assume that you can tell if the row is to be removed because column B is blank. 1. In an empty column enter the following formula =1/COUNTBLANK(B1) it will return #DIV/0! on all non-blank rows. 2. Copy this down as far as necessary. 3. With these formula selected press F5 (or Ctrl_G), Special, Formulas, and uncheck everything Errors. This will select all the cells in the column containing 1. 4. Press Ctrl- (Ctrl + Minus) and choose Entire Row. You can record all of this for your macro. -- Cheers, Shane Devenshire "DanF" wrote: Hey guys I'm trying to find a work around to the following problem. I'm trying to merge the data from numerous sheets into one sheet. I can copy the data into one sheet, but am trying to eliminate blank rows. I've used the VB code below, but beacuse the rows all contain formulas, the 'SpecialCells(xlCellTypeBlanks)' doesn't evaluate these cells as blank: Code: Sheets(combinedEMParams).Range("A2:D" & Range("D" & Rows.Count).End(xlUp).Row).SpecialCells(xlCellType Blanks).EntireRow.Delete Is there a 'xlCellTypeValueBlank' or something similar? Thanks for your help Dan |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Work around to SpecialCells(xlCellTypeBlanks)...
Is column d the one you want to evaluate to determine if the row
should be deleted? Cliff Edwards |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Work around to SpecialCells(xlCellTypeBlanks)...
Sub remove()
Dim c As Range For Each c In ActiveSheet.Range("d2:d" & Rows.Count) If c.Text = "" Then c.EntireRow.Delete Next End Sub Cliff Edwards |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Work around to SpecialCells(xlCellTypeBlanks)...
Hi Shane
Thanks a lot for the advice, will have a play! Ta "ShaneDevenshire" wrote: Hi Dan, I'm assuming that the cells with formulas appear blank because they display "", is that correct? If so Assume that you can tell if the row is to be removed because column B is blank. 1. In an empty column enter the following formula =1/COUNTBLANK(B1) it will return #DIV/0! on all non-blank rows. 2. Copy this down as far as necessary. 3. With these formula selected press F5 (or Ctrl_G), Special, Formulas, and uncheck everything Errors. This will select all the cells in the column containing 1. 4. Press Ctrl- (Ctrl + Minus) and choose Entire Row. You can record all of this for your macro. -- Cheers, Shane Devenshire "DanF" wrote: Hey guys I'm trying to find a work around to the following problem. I'm trying to merge the data from numerous sheets into one sheet. I can copy the data into one sheet, but am trying to eliminate blank rows. I've used the VB code below, but beacuse the rows all contain formulas, the 'SpecialCells(xlCellTypeBlanks)' doesn't evaluate these cells as blank: Code: Sheets(combinedEMParams).Range("A2:D" & Range("D" & Rows.Count).End(xlUp).Row).SpecialCells(xlCellType Blanks).EntireRow.Delete Is there a 'xlCellTypeValueBlank' or something similar? Thanks for your help Dan |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Work around to SpecialCells(xlCellTypeBlanks)...
Hi Cliff
Thanks for that, I'm got another idea from another fella as well, so I'm gonna give both ideas a try. Thanks for your time! "ward376" wrote: Sub remove() Dim c As Range For Each c In ActiveSheet.Range("d2:d" & Rows.Count) If c.Text = "" Then c.EntireRow.Delete Next End Sub Cliff Edwards |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Counting dates in multiple work sheets and work books | Excel Discussion (Misc queries) | |||
Specialcells | Charts and Charting in Excel | |||
Is there away to keep "auto save" from jumping to the first work sheet in the work book? | New Users to Excel | |||
VBA ON ERROR does not work with SPECIALCELLS | Excel Discussion (Misc queries) | |||
Spin button in a work sheet - how do I make it work? | Excel Worksheet Functions |