Work around to SpecialCells(xlCellTypeBlanks)...

[DanF]

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" & Range("D" &
Rows.Count).End(xlUp).Row).SpecialCells(xlCellTypeBlanks).EntireRow.Delete


Is there a 'xlCellTypeValueBlank' or something similar?

Thanks for your help
Dan

 

[ward376]

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

 

[DanF]

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?

 

[ShaneDevenshire]

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.

 

[ward376]

Is column d the one you want to evaluate to determine if the row
should be deleted?

Cliff Edwards

 

[ward376]

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

 

[DanF]

Hi Shane

Thanks a lot for the advice, will have a play!

Ta

 

[DanF]

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!