Delete lines with a blank cell

[F]

I have a worksheet, 'addresses', with two columns. Column A contains
formulae of the form
=IF(Aggregation!DD9<>"",Aggregation!DD9,"")
|
|
|
=IF(Aggregation!DD1100<>"",Aggregation!DD1100,"")

and column B contains
=IF(Aggregation!J9<>"",Aggregation!J9,"")
|
|
|
=IF(Aggregation!J1100<>"",Aggregation!J1100,"")

Some of the cells in column B are blank as nothing was entered in the
linked cell in the Aggregation sheet. I would like to be able to remove
all lines that have a blank cell in column B and then move the following
lines up to fill the gaps but have been unsuccessful with the macros I
have tried so far as the cells are not empty in that they contain a formula.

Is what I wish to do possible? Any help would be welcome!

TIA

 

[Rick Rothstein]

I have a worksheet, 'addresses', with two columns. Column A contains

formulae of the form
=IF(Aggregation!DD9<>"",Aggregation!DD9,"")
|
|
|
=IF(Aggregation!DD1100<>"",Aggregation!DD1100,"")

and column B contains
=IF(Aggregation!J9<>"",Aggregation!J9,"")
|
|
|
=IF(Aggregation!J1100<>"",Aggregation!J1100,"")

Some of the cells in column B are blank as nothing was entered in the
linked cell in the Aggregation sheet. I would like to be able to remove
all lines that have a blank cell in column B and then move the following
lines up to fill the gaps but have been unsuccessful with the macros I
have tried so far as the cells are not empty in that they contain a
formula.

Give this macro a try...

Sub RemoveRowWithBlankColumnB()
Dim LastRow As Long, LastColPlusOne As Long
LastRow = Cells.Find(What:="*", SearchOrder:=xlRows, _
SearchDirection:=xlPrevious, LookIn:=xlFormulas).Row
LastColPlusOne = Cells.Find(What:="*", SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, LookIn:=xlFormulas).Column
On Error GoTo NoCells
Application.ScreenUpdating = False
With Columns(LastColPlusOne)
.Value = Columns("B").Value
.Resize(LastRow).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
.Clear
End With
NoCells:
Application.ScreenUpdating = True
End Sub

Rick Rothstein (MVP - Excel)

 

[F]

Give this macro a try...

Sub RemoveRowWithBlankColumnB()
Dim LastRow As Long, LastColPlusOne As Long
LastRow = Cells.Find(What:="*", SearchOrder:=xlRows, _
SearchDirection:=xlPrevious, LookIn:=xlFormulas).Row
LastColPlusOne = Cells.Find(What:="*", SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, LookIn:=xlFormulas).Column
On Error GoTo NoCells
Application.ScreenUpdating = False
With Columns(LastColPlusOne)
.Value = Columns("B").Value
.Resize(LastRow).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
.Clear
End With
NoCells:
Application.ScreenUpdating = True
End Sub

Rick Rothstein (MVP - Excel)

Hi

Many thanks for the above.

It does, indeed, remove all blank rows but, unfortunately, it also
removes the contents of those cells in column B which contain data to
leave just the contents of column A intact.

 

[Vacuum Sealed]

Hi F

I have tested both If .Value statements and they work as they should
depending on your requirement.


Sub Remove_Blanks()

Dim Firstrow As Long
Dim Lastrow As Long
Dim Lrow As Long

With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
End With

With Sheets("Addresses").Select
Firstrow = .UsedRange.Cells(2).Row
Lastrow = .UsedRange.Rows(.UsedRange.Rows.Count).Row

For Lrow = Lastrow To Firstrow Step -1

With .Cells(Lrow, "B")

If Not IsError(.Value) Then

'use this if you want to retain blank cell(s) in Column
B with formulas
If .Value = "" Then
..Resize(Lastrow).SpecialCells(xlCellTypeBlanks).EntireRow.Delete

'use this if you want to delete the row(s) regardless
If .Value = "" Then .EntireRow.Delete

End If

End With

Next Lrow

End With

With Application
CalcMode = .Calculation
.Calculation = xlAutomatic
End With

End Sub

HTH
Mick.

 

[Rick Rothstein]

It does, indeed, remove all blank rows but, unfortunately, it

also removes the contents of those cells in column B which
contain data to leave just the contents of column A intact.

I'm sorry, but I am not clear as to what you meant by "line" in your Subject
or question as "line" is not an Excel term (except when referring to the
Line shape). Whenever someone says line in the context you did, they mean
"row", so I gave you code to remove (entire) rows. Okay, so that is not what
you want to do. If I understand you last message correctly, you are saying
if a cell in Column B is blank, then delete from Column B up to the last
data column for that particular row? If so, the here is the modification to
my previously posted code to accomplish this...

Sub RemoveRowWithBlankColumnB()
Dim LastRow As Long, LastColPlusOne As Long, DataRange As Range
Const DataStartRow As Long = 9
LastRow = Cells.Find(What:="*", SearchOrder:=xlRows, _
SearchDirection:=xlPrevious, LookIn:=xlFormulas).Row
LastColPlusOne = Cells.Find(What:="*", SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, LookIn:=xlFormulas).Column + 1
On Error GoTo NoCells
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
With Intersect(Range(DataStartRow & ":" & LastRow),
Columns(LastColPlusOne))
.Value = Intersect(Columns("B"), .EntireRow).Value
Intersect(Range("B" & DataStartRow & ":" & Split(Cells(1, _
Columns.Count).Address, "$")(1) & LastRow), .Resize( _
LastRow).SpecialCells(xlCellTypeBlanks).EntireRow).Delete
.Clear
End With
NoCells:
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub


Rick Rothstein (MVP - Excel)

 

[F]

I'm sorry, but I am not clear as to what you meant by "line" in your
Subject or question as "line" is not an Excel term (except when
referring to the Line shape). Whenever someone says line in the context
you did, they mean "row", so I gave you code to remove (entire) rows.
Okay, so that is not what you want to do. If I understand you last
message correctly, you are saying if a cell in Column B is blank, then
delete from Column B up to the last data column for that particular row?
If so, the here is the modification to my previously posted code to
accomplish this...

Sub RemoveRowWithBlankColumnB()
Dim LastRow As Long, LastColPlusOne As Long, DataRange As Range
Const DataStartRow As Long = 9
LastRow = Cells.Find(What:="*", SearchOrder:=xlRows, _
SearchDirection:=xlPrevious, LookIn:=xlFormulas).Row
LastColPlusOne = Cells.Find(What:="*", SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, LookIn:=xlFormulas).Column + 1
On Error GoTo NoCells
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
With Intersect(Range(DataStartRow & ":" & LastRow),
Columns(LastColPlusOne))
.Value = Intersect(Columns("B"), .EntireRow).Value
Intersect(Range("B" & DataStartRow & ":" & Split(Cells(1, _
Columns.Count).Address, "$")(1) & LastRow), .Resize( _
LastRow).SpecialCells(xlCellTypeBlanks).EntireRow).Delete
.Clear
End With
NoCells:
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub


Rick Rothstein (MVP - Excel)

Apologies for the loose use of language! My references to 'line' were,
as you realised, references to 'row'.

Your modified macro almost does what I wanted: thank you.

I was looking to check a cell in column B and, if it is blank (it
contains just the underlying formula and no text brought in by that
formula from another sheet), then the whole of that row, including cells
A and B, should be deleted and the rows below moved up to fill the gap
newly introduced. This should be repeated all the way down the sheet to
check all rows.

At the moment, the macro removes all rows with blank cells in column B
but leaves column A intact. This means that when column B (minus all
blank cells in column B) is written back to the sheet the 'links'
between cells in the 'old' column B and column A are lost. An example in
an effort to explain myself(!):

prior to the macro being run
A1 holds YELLOW ...... B1 holds CUSTARD
A2 GREEN B2 [blank]
A3 BLUE B3 SKY
A4 ORANGE B4 FRUIT
A5 BLACK B5 NIGHT

after the current macro has been run we have
A1 YELLOW B1 CUSTARD
A2 GREEN B2 SKY
A3 BLUE B3 FRUIT
A4 ORANGE B4 NIGHT
A5 BLACK B5 CHEESE

rather than the desired
A1 YELLOW B1 CUSTARD
A2 BLUE B2 SKY
A3 ORANGE B3 FRUIT
A4 BLACK B4 NIGHT
A5 WHITE B5 CHEESE

Again, many thanks for your help!

 

[F]

Hi F

I have tested both If .Value statements and they work as they should
depending on your requirement.


Sub Remove_Blanks()

Dim Firstrow As Long
Dim Lastrow As Long
Dim Lrow As Long

With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
End With

With Sheets("Addresses").Select
Firstrow = .UsedRange.Cells(2).Row
Lastrow = .UsedRange.Rows(.UsedRange.Rows.Count).Row

For Lrow = Lastrow To Firstrow Step -1

With .Cells(Lrow, "B")

If Not IsError(.Value) Then

'use this if you want to retain blank cell(s) in Column
B with formulas
If .Value = "" Then
.Resize(Lastrow).SpecialCells(xlCellTypeBlanks).EntireRow.Delete

'use this if you want to delete the row(s) regardless
If .Value = "" Then .EntireRow.Delete

End If

End With

Next Lrow

End With

With Application
CalcMode = .Calculation
.Calculation = xlAutomatic
End With

End Sub

HTH
Mick.

Thanks for this : appreciated.

Unfortunately, I'm getting a "Run-time error '424': Object required"
error whichever version I use.

The line

Firstrow = .UsedRange.Cells(2).Row

is highlighted each time and my knowledge of VB isn't sufficient to
understand what is happening!

 

[F]

I'm sorry, but I am not clear as to what you meant by "line" in your
Subject or question as "line" is not an Excel term (except when
referring to the Line shape). Whenever someone says line in the context
you did, they mean "row", so I gave you code to remove (entire) rows.
Okay, so that is not what you want to do. If I understand you last
message correctly, you are saying if a cell in Column B is blank, then
delete from Column B up to the last data column for that particular row?
If so, the here is the modification to my previously posted code to
accomplish this...

Sub RemoveRowWithBlankColumnB()
Dim LastRow As Long, LastColPlusOne As Long, DataRange As Range
Const DataStartRow As Long = 9
LastRow = Cells.Find(What:="*", SearchOrder:=xlRows, _
SearchDirection:=xlPrevious, LookIn:=xlFormulas).Row
LastColPlusOne = Cells.Find(What:="*", SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, LookIn:=xlFormulas).Column + 1
On Error GoTo NoCells
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
With Intersect(Range(DataStartRow & ":" & LastRow),
Columns(LastColPlusOne))
.Value = Intersect(Columns("B"), .EntireRow).Value
Intersect(Range("B" & DataStartRow & ":" & Split(Cells(1, _
Columns.Count).Address, "$")(1) & LastRow), .Resize( _
LastRow).SpecialCells(xlCellTypeBlanks).EntireRow).Delete
.Clear
End With
NoCells:
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub


Rick Rothstein (MVP - Excel)

Apologies for the loose use of language! My references to 'line' were,
as you realised, references to 'row'.

Your modified macro almost does what I wanted: thank you.

I was looking to check a cell in column B and, if it is blank (it
contains just the underlying formula and no text brought in by that
formula from another sheet), then the whole of that row, including cells
A and B, should be deleted and the rows below moved up to fill the gap
newly introduced. This should be repeated all the way down the sheet to
check all rows.

At the moment, the macro removes all rows with blank cells in column B
but leaves column A intact. This means that when column B (minus all
blank cells in column B) is written back to the sheet the 'links'
between cells in the 'old' column B and column A are lost. An example in
an effort to explain myself(!):

prior to the macro being run
A1 holds YELLOW ...... B1 holds CUSTARD
A2 GREEN B2 [blank]
A3 BLUE B3 SKY
A4 ORANGE B4 FRUIT
A5 BLACK B5 NIGHT

after the current macro has been run we have
A1 YELLOW B1 CUSTARD
A2 GREEN B2 SKY
A3 BLUE B3 FRUIT
A4 ORANGE B4 NIGHT
A5 BLACK B5 CHEESE

rather than the desired
A1 YELLOW B1 CUSTARD
A2 BLUE B2 SKY
A3 ORANGE B3 FRUIT
A4 BLACK B4 NIGHT
A5 WHITE B5 CHEESE

Again, many thanks for your help!

Found it!

I have changed

Intersect(Range("B" & DataStartRow

etc to

Intersect(Range("A" & DataStartRow

and it does exactly what I wanted.