For..Next loop error

I

Ian_Limbo

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
 
B

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

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

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
 
I

Ian_Limbo

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
 
I

Ian_Limbo

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

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

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
 
B

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

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?
 
T

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

R. Choate said:
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
 
I

Ian_Limbo

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
 
I

Ian_Limbo

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
 
T

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top