Macro only "Works" with F-8

[Dave Birley]

Here's the Macro:

Sub DeleteBlanksInColumnA()
'
' Macro1 Macro
' Macro recorded 5/17/2007 by Administrator
'
Dim rngCell, rngNewCell As Range
Dim varName, varCount As Variant

varCount = 1
Sheets("Names by Category").Select
With Range("A2", Range("A" & Rows.Count).End(xlUp))
For Each rngCell In .Cells
If IsEmpty(rngCell(1, 1)) Then
rngCell(1, 1).Select
Selection.Delete Shift:=xlUp
End If
Next rngCell
End With
End Sub

When I step through it with the F8 key, it does what I expect it to do. When
I turn it loose with Run, it don't do nuffin'!

Whutz goin' on here?

 

[Mike H]

Dave,

the macro works perfectly for me no matter how I run it. I assume you expect
it to delete empty cells in the used range in column A.

Mike

Mike

 

[JW]

Works fine for me when I run it. There isn't a need to select the
cells though. Depending on the size of your data, that could make a
slight impact in runtime. Also, you have 3 variables declared that
you aren't using. There is probably a reason for this, but thought
I'd mention it anyhow. Here is some tweaked code without the cell
selection.
Sub DeleteBlanksInColumnA()
Dim rngCell, rngNewCell As Range
Dim varName, varCount As Variant
varCount = 1
Sheets("Names by Category").Select
With Range("A2", Range("A" & Rows.Count).End(xlUp))
For Each rngCell In .Cells
If IsEmpty(rngCell(1, 1)) Then _
rngCell(1, 1).Delete Shift:=xlUp
Next rngCell
End With
End Sub

HTH

 

[PCLIVE]

It works for me unless there are consecutive blank rows in column A. Then
it will miss one of them.
Try this:

Sub DeleteBlanksInColumnA()

Dim rngCell, rngNewCell As Range
Dim varName, varCount As Variant

varCount = 1
Sheets("Names by Category").Select
With Range("A2", Range("A" & Rows.Count).End(xlUp))
For Each rngCell In .Cells
If IsEmpty(rngCell(1, 1)) Then
setUnion = setUnion & rngCell(1, 1).Address & ","
End If
Next rngCell
End With

If Len(setUnion) > 0 _
Then
setUnion = Left(setUnion, Len(setUnion) - 1)
Range(setUnion).Select
Selection.Delete shift:=xlUp
Else
End If
End Sub


Regards,
Paul

 

[Dave Birley]

Odd -- those extra variables were the result of me starting by hacking
another Macro and just stealing its first 10 lines or so, then not being
careful with my "housekeeping". As the number of Rows involved was very small
(<200), I just held my finger down on the F8 key and did the dirty deed.
However I shall hang on to your tweak for future reference. Thanks for the
help!
--
Dave
Temping with Staffmark
in Rock Hill, SC

Works fine for me when I run it. There isn't a need to select the
cells though. Depending on the size of your data, that could make a
slight impact in runtime. Also, you have 3 variables declared that
you aren't using. There is probably a reason for this, but thought
I'd mention it anyhow. Here is some tweaked code without the cell
selection.
Sub DeleteBlanksInColumnA()
Dim rngCell, rngNewCell As Range
Dim varName, varCount As Variant
varCount = 1
Sheets("Names by Category").Select
With Range("A2", Range("A" & Rows.Count).End(xlUp))
For Each rngCell In .Cells
If IsEmpty(rngCell(1, 1)) Then _
rngCell(1, 1).Delete Shift:=xlUp
Next rngCell
End With
End Sub

HTH

 

[Dave Birley]

Thanks for the input. Programming can be a lot like genealogy -- negative
information ("it works for me") is just as important as the positive.

 

[Dave Birley]

«It works for me unless there are consecutive blank rows in column A.»

Yup -- that was it! Thanks for catching it.

 

[Dave Peterson]

It usually makes life a lot simpler to process the data from the bottom row to
the top row.

Option Explicit
Sub testme01()
Dim FirstRow as long
dim LastRow as long
dim iRow as long

with worksheets("names by category")
firstrow = 2
lastrow = .cells(.rows.count,"A").end(xlup).row

for irow = lastrow to firstrow step -1
if isempty(.cells(irow,"A").value) then
.cells(irow,"A").delete shift:=xlup
'or delete the entire row
'.rows(irow).delete
end if
next irow
end with
end sub

========
Alternatively, you could do this:

Manually:
select the range (A2:Axx)
edit|goto|special|blanks
edit|delete|Shift cells up

In code:
Option Explicit
Sub testme02()

With Worksheets("names by category")
On Error Resume Next
.Range("a2", .Cells(.Rows.Count, "A").End(xlUp)) _
.Cells.SpecialCells(xlCellTypeBlanks).Delete Shift:=xlUp
On Error GoTo 0
End With
End Sub

To delete the entire row, use this:

.Range("a2", .Cells(.Rows.Count, "A").End(xlUp)) _
.Cells.SpecialCells(xlCellTypeBlanks).entirerow.delete

As an aside, if you have empty cells in column A after the last used cell in
column A, then those cells/rows won't be deleted with any of this code. You'd
have to find the lastrow some other way than relying on column A.

 

[Dave Birley]

Once again I become very conscious of the fact that, although I understand
the basic (no pun intended) principles of programming, their translation into
different languages is as complex as when I am trying to translate from
Swahili to French, with English as my native tongue!

Having an "interpreter" handy is most helpful <g>! Thanks.