A simple Question

B

Broxterman

I am a little new to macros and I am having trouble using a suggested
macro in my spreadsheet. My simple question is what do I need to do to
change the following macro so that it will work on my spreadsheet.

Sub DeleteEmptyRows(DeleteRange As Range)
' Deletes all empty rows in DeleteRange
' Example: DeleteEmptyRows Selection
' Example: DeleteEmptyRows Range("A1:D100")
Dim rCount As Long, r As Long
If DeleteRange Is Nothing Then Exit Sub
If DeleteRange.Areas.Count > 1 Then Exit Sub
With DeleteRange
rCount = .Rows.Count
For r = rCount To 1 Step -1
If Application.CountA(.Rows(r)) = 0 Then
..Rows(r).EntireRow.Delete
End If
Next r
End With
End Sub

I simply want to delete every row in column B5:20 in my spreadsheet
that does not contain a value. Please let me know what I need to
insert into the above macro to do this.
Thanks so much.
 
F

Frank Kabel

Hi
try the following (will delete all rows, if column B for this row is
empty / does not check if any other column contains a value)

Sub Delete_col_B()

Dim rCount As Long, r As Long
Set DeleteRange = Range("B5:B20")
If DeleteRange Is Nothing Then Exit Sub
With DeleteRange
rCount = .Rows.Count
For r = rCount To 1 Step -1
If cells(r,"B") = "" Then
Rows(r).EntireRow.Delete
End If
Next r
End With
End Sub
 
A

AlfD

Can I check: do you want to delete the entire row or just the cell i
column B if it is empty? (e.g. moving the rest of the column up to fil
in gaps)

Al
 
F

Frank Kabel

Hi Alf
this deletes the ENTIRE row. If you only want to delete the cell in
column B try the following

Dim rCount As Long, r As Long
Set DeleteRange = Range("B5:B20")
If DeleteRange Is Nothing Then Exit Sub
With DeleteRange
rCount = .Rows.Count
For r = rCount To 1 Step -1
If cells(r,"B") = "" Then
cells(r,"B").Delete Shift:=xlUp
End If
Next r
End With
End Sub
 
B

Broxterman

Thanks so much Frank, that macro works perfectly. However, the row
that I want to delete have formulas in them and therefore are no
deleted. Is there a way to delete the row even if it has a formula i
it??
Thank
 
F

Frank Kabel

Hi
do the formulas return a result and if yes what result (you may post an
example for one of your formulas)
If they return "" this rows should also be deleted
 
B

Broxterman

The formulas are for example if(A1=1,B1," ")
If A1=1, I want the vaule in B1 to appear in my list, if not, I wan
nothing to appear, which is what happens. The macro works perfectly o
an empty cell, but it doesn't work with that formula.

In addition, when I specify a deleterange, for example "B5:20", it als
deletes any rows above it as well(B1:4).

Your help is appreciated.
Larr
 
Top