A simple Question

  • Thread starter Thread starter Broxterman
  • Start date Start date
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.
 
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
 
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
 
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
 
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
 
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
 
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
 
Hi
change the formula
if(A1=1,B1," ")

to
if(A1=1,B1,"")

Note: I recommend you always return "" instead of " " (a space) in such
formulas as a space is not an empty cell. If you change the formulas
the macro will also delete these rows
 
Back
Top