For..Next loop error

[Guest]

I am attempting to compress a column of data by deleting blank cells that has
lots of gaps in it. It errors at no. 256 on the for loop.

The code i am using is:

Sub CommandButton1_Click()
Dim i As Double
Dim cellcontent As String
Range("A2").Select
MsgBox "Cell selected"
For i = 2 To 2150 Step 1
cellcontent = Sheets("Selection List").Cells(1, i)
Do While ActiveCell.Value = ""
Selection.Delete
Loop
ActiveCell.Offset(1, 0).Select
Next i
MsgBox "Selection list complete"
End Sub

All help is gratefully received (this is my first attempt at VBA)

Ian

 

[Don Guillett]

Maybe because there are no more columns after 255???

 

[Bernie Deitrick]

Ian,

No need to loop:

Range("A2:A2150").SpecialCells(xlCellTypeBlanks).Delete

To keep things moving together:

Range("A2:A2150").SpecialCells(xlCellTypeBlanks).EntireRow.Delete

HTH,
Bernie
MS Excel MVP

 

[R. Choate]

This bit of code will select all of the blank cells in the specified range, which you could then delete all at once.

Columns("E:E").Select
Selection.SpecialCells(xlCellTypeBlanks).Select

I used the range "E:E", but any range would work the same.

Selection.Delete Shift:=xlUp 'this will delete the cells only and shift everything up

or

Selection.EntireRow.Delete 'which is self explanatory

Perhaps this will save you some unnecessary code.
--
RMC,CPA


I am attempting to compress a column of data by deleting blank cells that has
lots of gaps in it. It errors at no. 256 on the for loop.

The code i am using is:

Sub CommandButton1_Click()
Dim i As Double
Dim cellcontent As String
Range("A2").Select
MsgBox "Cell selected"
For i = 2 To 2150 Step 1
cellcontent = Sheets("Selection List").Cells(1, i)
Do While ActiveCell.Value = ""
Selection.Delete
Loop
ActiveCell.Offset(1, 0).Select
Next i
MsgBox "Selection list complete"
End Sub

All help is gratefully received (this is my first attempt at VBA)

Ian

 

[R. Choate]

How stupid of me not to spot that. When I saw your reply I just popped myself on the forehead. How obvious could it be? Props to you
for spotting the column limit instead of just telling the guy a quicker way to find the blanks like some morons (me) did.

You are a sharp guy and always give good help in these forums.

Richard

 

[Guest]

Thanks, the code works great, except the column is a paste special, so it
does not see the blank cells as actually blank. Is there anyway round this?

Ian

 

[Guest]

If i am deleting rows in a column, which is what appears to be happening,
where does the column limit problem come in?

 

[Tom Ogilvy]

for i = 2150 to 2 step -1
if cells(i,1).Value = "" then
cells(i,1).EntireRow.Delete
' or
' cells(i,1).Delete Shift:=xlShiftUp
end if
Next

 

[R. Choate]

The code you posted doesn't show any copy/paste activity unless my eyes are just too blurry today. Do you have some more code you
could post?

--
RMC,CPA


Thanks, the code works great, except the column is a paste special, so it
does not see the blank cells as actually blank. Is there anyway round this?

Ian

 

[Bernie Deitrick]

Ian,

You are indexing through columns using the Cells(1, i), which should be
Cells(i,1) to index through rows.

HTH,
Bernie
MS Excel MVP

 

[R. Choate]

because your loop is crawling to the right with the line

cellcontent = Sheets("Selection List").Cells(1, i)

Once i gets bigger than 255 it is going to throw an error because anything larger than Cells(1,256) doesn't exist
--
RMC,CPA


If i am deleting rows in a column, which is what appears to be happening,
where does the column limit problem come in?

 

[Tom Ogilvy]

He means the column being processed has been treated with paste special =>
values. for cells that contained a formula like

=if(condition,"",somethingelse)

if it was meeting the condition (and thus appeared empty), then when paste
special is done, it leaves a null string in the cell. The cell is not empty
and will not be picked up by Specialcells(xlblanks) even though it looks
empty.

--
Regards,
Tom Ogilvy

The code you posted doesn't show any copy/paste activity unless my eyes

are just too blurry today. Do you have some more code you

 

[Guest]

Bingo! Thanks very much! It works now - if a little slow, but i need to code
up the copy and pasteing (its manual at the moment), before i transfer in the
smarter code. This is my first dabble at sorting out repititive tasks that i
have to do.

Many thanks again

Ian

 

[Guest]

That's exactly how it is, and its the next task i'm tackling to make the tool
more efficient.

Thanks for all your help

Ian

 

[Tom Ogilvy]

I suspect you are looping all the way to the bottom of the sheet and thus
that is why it is so slow. Looping from the highest numbered row back to
row 2 will solve that problem although it isn't clear exactly what you are
doing besides deleting blanks.