Deleting Empty Rows on 3 worksheets

[ajc5382]

Hi,

I currently have a program written that pulls data onto 3 separate worksheets. I'm looking for code I can put into a new module that will delete rows with with no data in each worksheet.

I have the following, however when I run my program as a whole I get an error stating "nocells"

Sub DelBlankRows()

Worksheets("pwr").Columns(2).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
Worksheets("pwrone").Columns(2).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
Worksheets("pwrtwo").Columns(2).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End Sub

 

[Jonmo1]

This means your blanks are not truly blank.
I suspect they are formulas that return ""
Or they were such formulas at one time, but have since been copy / pastespecial / values

Try

Sub DelBlankRows()
Dim ws As Worksheet
For Each ws In Worksheets
    Select Case ws.Name
        Case "pwr", "pwrone", "pwrtwo"
            With ws.Columns(2)
                .Value = .Value
                .SpecialCells(xlCellTypeBlanks).EntireRow.Delete
            End With
    End Select
Next ws
End Sub

 

[ajc5382]

That didn't seem to work. I checked all worksheets reference names, everything seemed to match up, yet no rows were deleted.

 

[Jonmo1]

Well, the issue is still the same.
Your blanks are not really blank.

Pick one of the cells that appear to be blank.
for this example, say it's B10

What do these formulas return

=ISBLANK(B10)
=LEN(B10)
=CODE(LEFT(B10,1))

 

[ajc5382]

I received a 0, a 3, and a 0

 

[ajc5382]

Jonmo,

I've figured out a way to delete some of my data. One issue I could use some help with is deleting rows of data from holidays. I have a list in excel of holidays for the next several years. Do you know of a way to quickly check the date in the data pull will my list of holidays? Possibly then delete that row?

Well, the issue is still the same.
Your blanks are not really blank.

Pick one of the cells that appear to be blank.
for this example, say it's B10

What do these formulas return

=ISBLANK(B10)
=LEN(B10)
=CODE(LEFT(B10,1))

 

[memar_one]

Well, the issue is still the same.
Your blanks are not really blank.

Pick one of the cells that appear to be blank.
for this example, say it's B10

What do these formulas return

=ISBLANK(B10)
=LEN(B10)
=CODE(LEFT(B10,1))

I received a 0, a 3, and a 0

Is this possible? Isblank is false, there's a length to the string, but the left character is null...?