Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Befuddled with For Next Loop ------ Run - Time Error '13' Type Mismatch Error | Excel Programming | |||
Error in loop | Excel Programming | |||
loop error | Excel Programming | |||
loop error | Excel Programming | |||
loop error | Excel Programming |