For..Next loop error
 

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

For..Next loop error
 

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

--
Don Guillett
SalesAid Software

"Ian_Limbo" wrote in message
...
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

For..Next loop error
 

Ian,

No need to loop:

Range("A2:A2150").SpecialCells(xlCellTypeBlanks).D elete

To keep things moving together:

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

HTH,
Bernie
MS Excel MVP

"Ian_Limbo" wrote in message
...
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

For..Next loop error
 

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


"Ian_Limbo" wrote in message ...
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

For..Next loop error
 

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
--
RMC,CPA


"Don Guillett" wrote in message ...
Maybe because there are no more columns after 255???

--
Don Guillett
SalesAid Software

"Ian_Limbo" wrote in message
...
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

For..Next loop error
 

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

"R. Choate" wrote:

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


"Ian_Limbo" wrote in message ...
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

For..Next loop error
 

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

"Don Guillett" wrote:

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

--
Don Guillett
SalesAid Software

"Ian_Limbo" wrote in message
...
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

For..Next loop error
 

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

--
Regards,
Tom Ogilvy


"Ian_Limbo" wrote in message
...
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

"R. Choate" wrote:

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


"Ian_Limbo" wrote in message
...
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

For..Next loop error
 

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


"Ian_Limbo" wrote in message ...
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

"R. Choate" wrote:

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


"Ian_Limbo" wrote in message ...
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

For..Next loop error
 

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

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

"Don Guillett" wrote:

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

--
Don Guillett
SalesAid Software

"Ian_Limbo" wrote in message
...
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

For..Next loop error
 

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


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

"Don Guillett" wrote:

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

--
Don Guillett
SalesAid Software

"Ian_Limbo" wrote in message
...
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

For..Next loop error
 

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" wrote in message
...
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


"Ian_Limbo" wrote in message
...
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

"R. Choate" wrote:

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


"Ian_Limbo" wrote in message
...
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

For..Next loop error
 

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

"R. Choate" wrote:

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


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

"Don Guillett" wrote:

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

--
Don Guillett
SalesAid Software

"Ian_Limbo" wrote in message
...
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

For..Next loop error
 

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" wrote:

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" wrote in message
...
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


"Ian_Limbo" wrote in message
...
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

"R. Choate" wrote:

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


"Ian_Limbo" wrote in message
...
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

For..Next loop error
 

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.

--
Regards,
Tom Ogilvy

"Ian_Limbo" wrote in message
...
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

"R. Choate" wrote:

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


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

"Don Guillett" wrote:

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

--
Don Guillett
SalesAid Software

"Ian_Limbo" wrote in message
...
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