Deleting blank rows with formulas in them

[Kennedy]

I have a spreadsheet that takes data from one worksheet and moves it to
several worksheets, depending on a certain value. There are several
worksheets where the data has been moved, based on a macro and formula I
wrote.
There are several rows that will always be blank, however a formula appears
in those rows. Is there a way to create a macro that will look at all the
"named" sheets individually and find any rows from 8-300 that have a formula
in them, but do not contain actual data.
For instance, worksheet NEO has formulas that feed off another sheet from
row 8-300. However, only data appears in rows 8-125. Rows 126-300 are blank
but contain a formula. How can that e deleted.
Same thing with worksheet OEN, except data is present in rows 8-100, with
101-300 being blank but containint formulas.
Same with other worksheets.

 

[Mike H]

Hi,

Which columns are we looking at in these rows, all of them?
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

 

[Jacob Skaria]

You can try out the below macro. If you are new to macros..

--Set the Security level to low/medium in (Tools|Macro|Security).
--From workbook launch VBE using short-key Alt+F11.
--From menu 'Insert' a module and paste the below code.
--Get back to Workbook.
--Run macro from Tools|Macro|Run <selected macro()>

Namedrange "SheetNames"..Change to suit

Sub DeleteRows()
Dim ws As Worksheet, cell As Range, lngRow As Long

For Each cell In Range("SheetNames")
Set ws = Sheets(cell.Text)

For lngRow = 24 To 8 Step -1
If WorksheetFunction.CountBlank(ws.Rows(lngRow)) = Columns.Count _
Then ws.Rows(lngRow).Delete
Next
Next
End Sub

 

[Kennedy]

Mike,
All Columns. In this case, I have formulas in rows 8-300, columns A-P. So if
the formulas returns data back in rows 8-100, columns A-P, keep that, but
delete all formulas from 101-300, columns A-P.
The one thing consistent in all worksheets is that the data starts on row 8,
and use columns A-P

 

[Kennedy]

Jacob,
Maybe I did something wrong. Changed the "SheetNames" to "GEO", which is one
of the worksheets that I have. Tried running the macro and returned an error:
Run-time error '1004':
Method 'Range' of object'_Global' failed.
When looking at the macro, the For Each cell In Range("GEO") is
highlighted in yellow

 

[Mike H]

Try this,

Change the list of sheet names to the ones you want to work on

Sub marine()
Dim ws As Worksheet
Dim MyRange As Range
Dim CopyRange As Range
S = "Sheet1,Sheet2,Sheet9" 'Change to suit
V = Split(S, ",")
For Each ws In ThisWorkbook.Worksheets
If Not IsError(Application.Match(ws.Name, V, 0)) Then
Set MyRange = ws.Range("A8:A800")
For Each c In MyRange
If WorksheetFunction.Count(Range("A" & c.Row & ":" & "P" & c.Row)) = 0
Then
If CopyRange Is Nothing Then
Set CopyRange = c.EntireRow
Else
Set CopyRange = Union(CopyRange, c.EntireRow)
End If
End If
Next
End If
If Not CopyRange Is Nothing Then
CopyRange.Delete
Set CopyRange = Nothing
End If
Next
End Sub
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

 

[Jacob Skaria]

Hi Kennedy

"Sheetnames" is a named range containing the sheet names you want to
delete.. If you are looking to delete just one sheet then try the below
version...


Sub DeleteRows()
Dim ws As Worksheet, lngRow As Long

Set ws = Sheets("GEO)

For lngRow = 24 To 8 Step -1
If WorksheetFunction.CountBlank(ws.Rows(lngRow)) = Columns.Count _
Then ws.Rows(lngRow).Delete
Next

End Sub

 

[Mike H]

oops,,
There's a bug in that, try this instead

Sub marine()
Dim ws As Worksheet
Dim MyRange As Range
Dim CopyRange As Range
S = "Sheet1,Sheet2,Sheet9" 'Change to suit
V = Split(S, ",")
For Each ws In ThisWorkbook.Worksheets
If Not IsError(Application.Match(ws.Name, V, 0)) Then
Set MyRange = ws.Range("A8:A800" & lastrow)
For Each c In MyRange
If WorksheetFunction.Count(ws.Range("A" & c.Row & ":" & "P" & c.Row)) =
0 Then
If CopyRange Is Nothing Then
Set CopyRange = c.EntireRow
Else
Set CopyRange = Union(CopyRange, c.EntireRow)
End If
End If
Next
End If
If Not CopyRange Is Nothing Then
CopyRange.Delete
Set CopyRange = Nothing
End If
Next
End Sub
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.