Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop Skips Data - Why?
Here is my coding and when it goes through the loop it makes it through the
entire list of numbers but always misses the last zero which is what I am trying to delete. Any ideas as to why this is happening? The msgbox is in there just so I could see what was going on in my code. Sub CopyTransferData() ' ' CopyTransferData Macro ' Copy totals and eliminate zeros, move to Data tab. ' Dim rngCurrent As Range Dim rngCell As Range Dim result As Integer Dim shtRorkERP As Worksheet Set shtRorkERP = Application.ActiveWorkbook.Worksheets("Rork_ERP") ' ' Range("FirstIteration").Select Selection.Copy Range("C1").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Application.CutCopyMode = False Set rngCurrent = shtRorkERP.Range("C1").CurrentRegion Set rngCurrent = rngCurrent.Offset(rowoffset:=0, columnoffset:=1) Set rngCurrent = rngCurrent.Resize(columnsize:=1) For Each rngCell In rngCurrent result = rngCell.Value If rngCell.Value = 0 Then rngCell.EntireRow.Select rngCell.EntireRow.Delete End If MsgBox result Next rngCell End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop Skips Data - Why?
You need to do something like this for the delete
Dim myRange as range Set myRange = nothing For Each rngCell In rngCurrent result = rngCell.Value If rngCell.Value = 0 Then if myRange is nothing then Set myRange = rngCell else Set myRange = union(myRange,rngCell) end if MsgBox result Next rngCell if not myRange is nothing then myRange.entirerow.delete end if -- HTH, Barb Reinhardt If this post was helpful to you, please click YES below. "Walter" wrote: Here is my coding and when it goes through the loop it makes it through the entire list of numbers but always misses the last zero which is what I am trying to delete. Any ideas as to why this is happening? The msgbox is in there just so I could see what was going on in my code. Sub CopyTransferData() ' ' CopyTransferData Macro ' Copy totals and eliminate zeros, move to Data tab. ' Dim rngCurrent As Range Dim rngCell As Range Dim result As Integer Dim shtRorkERP As Worksheet Set shtRorkERP = Application.ActiveWorkbook.Worksheets("Rork_ERP") ' ' Range("FirstIteration").Select Selection.Copy Range("C1").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Application.CutCopyMode = False Set rngCurrent = shtRorkERP.Range("C1").CurrentRegion Set rngCurrent = rngCurrent.Offset(rowoffset:=0, columnoffset:=1) Set rngCurrent = rngCurrent.Resize(columnsize:=1) For Each rngCell In rngCurrent result = rngCell.Value If rngCell.Value = 0 Then rngCell.EntireRow.Select rngCell.EntireRow.Delete End If MsgBox result Next rngCell End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop Skips Data - Why?
Hi Walter, just to footnote Don's remarks, when deleting entire rows, the
code should start at the bottom of the used range to avoid skipping rows. The default shift after deleting a row is up. So if you have two consecutive rows that meet the delete criteria and you are progressing from top to bottom, it will miss that second row because after the delete, the second row is now the row with focus and the "Next" operator will select what was the third row, thereby skipping the second row which also contained the delete criteria. Even if you are using the Offset(1, 0) method in a Do...Loop, the effect is the same as it works downward. But when you work upward, the focus remains on the row beneath the next row to be evaluated. That is why Don's code works from the higher row number to the lower with Step -1. "Walter" wrote: Here is my coding and when it goes through the loop it makes it through the entire list of numbers but always misses the last zero which is what I am trying to delete. Any ideas as to why this is happening? The msgbox is in there just so I could see what was going on in my code. Sub CopyTransferData() ' ' CopyTransferData Macro ' Copy totals and eliminate zeros, move to Data tab. ' Dim rngCurrent As Range Dim rngCell As Range Dim result As Integer Dim shtRorkERP As Worksheet Set shtRorkERP = Application.ActiveWorkbook.Worksheets("Rork_ERP") ' ' Range("FirstIteration").Select Selection.Copy Range("C1").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Application.CutCopyMode = False Set rngCurrent = shtRorkERP.Range("C1").CurrentRegion Set rngCurrent = rngCurrent.Offset(rowoffset:=0, columnoffset:=1) Set rngCurrent = rngCurrent.Resize(columnsize:=1) For Each rngCell In rngCurrent result = rngCell.Value If rngCell.Value = 0 Then rngCell.EntireRow.Select rngCell.EntireRow.Delete End If MsgBox result Next rngCell End Sub |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop Skips Data - Why?
That's why I put the deleted cells in a new range and then delete them when
I'm done (if there's anything to delete) Barb Reinhardt "JLGWhiz" wrote: Hi Walter, just to footnote Don's remarks, when deleting entire rows, the code should start at the bottom of the used range to avoid skipping rows. The default shift after deleting a row is up. So if you have two consecutive rows that meet the delete criteria and you are progressing from top to bottom, it will miss that second row because after the delete, the second row is now the row with focus and the "Next" operator will select what was the third row, thereby skipping the second row which also contained the delete criteria. Even if you are using the Offset(1, 0) method in a Do...Loop, the effect is the same as it works downward. But when you work upward, the focus remains on the row beneath the next row to be evaluated. That is why Don's code works from the higher row number to the lower with Step -1. "Walter" wrote: Here is my coding and when it goes through the loop it makes it through the entire list of numbers but always misses the last zero which is what I am trying to delete. Any ideas as to why this is happening? The msgbox is in there just so I could see what was going on in my code. Sub CopyTransferData() ' ' CopyTransferData Macro ' Copy totals and eliminate zeros, move to Data tab. ' Dim rngCurrent As Range Dim rngCell As Range Dim result As Integer Dim shtRorkERP As Worksheet Set shtRorkERP = Application.ActiveWorkbook.Worksheets("Rork_ERP") ' ' Range("FirstIteration").Select Selection.Copy Range("C1").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Application.CutCopyMode = False Set rngCurrent = shtRorkERP.Range("C1").CurrentRegion Set rngCurrent = rngCurrent.Offset(rowoffset:=0, columnoffset:=1) Set rngCurrent = rngCurrent.Resize(columnsize:=1) For Each rngCell In rngCurrent result = rngCell.Value If rngCell.Value = 0 Then rngCell.EntireRow.Select rngCell.EntireRow.Delete End If MsgBox result Next rngCell End Sub |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop Skips Data - Why?
Hi Barb, I was just answering the OPs question of why it skips.
"Barb Reinhardt" wrote: That's why I put the deleted cells in a new range and then delete them when I'm done (if there's anything to delete) Barb Reinhardt "JLGWhiz" wrote: Hi Walter, just to footnote Don's remarks, when deleting entire rows, the code should start at the bottom of the used range to avoid skipping rows. The default shift after deleting a row is up. So if you have two consecutive rows that meet the delete criteria and you are progressing from top to bottom, it will miss that second row because after the delete, the second row is now the row with focus and the "Next" operator will select what was the third row, thereby skipping the second row which also contained the delete criteria. Even if you are using the Offset(1, 0) method in a Do...Loop, the effect is the same as it works downward. But when you work upward, the focus remains on the row beneath the next row to be evaluated. That is why Don's code works from the higher row number to the lower with Step -1. "Walter" wrote: Here is my coding and when it goes through the loop it makes it through the entire list of numbers but always misses the last zero which is what I am trying to delete. Any ideas as to why this is happening? The msgbox is in there just so I could see what was going on in my code. Sub CopyTransferData() ' ' CopyTransferData Macro ' Copy totals and eliminate zeros, move to Data tab. ' Dim rngCurrent As Range Dim rngCell As Range Dim result As Integer Dim shtRorkERP As Worksheet Set shtRorkERP = Application.ActiveWorkbook.Worksheets("Rork_ERP") ' ' Range("FirstIteration").Select Selection.Copy Range("C1").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Application.CutCopyMode = False Set rngCurrent = shtRorkERP.Range("C1").CurrentRegion Set rngCurrent = rngCurrent.Offset(rowoffset:=0, columnoffset:=1) Set rngCurrent = rngCurrent.Resize(columnsize:=1) For Each rngCell In rngCurrent result = rngCell.Value If rngCell.Value = 0 Then rngCell.EntireRow.Select rngCell.EntireRow.Delete End If MsgBox result Next rngCell End Sub |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop Skips Data - Why?
Thank you for your explanation of why it skips. I will remember from now on
to delete from the bottom up. I am learning VBA and it is great to have this discussion board. By the way, if my range were in a place where I had to delete cells rather than an entire row, how would I do so: ColC ColD Name1 100 Name2 365 Name3 0 Name4 10 Name5 0 Name6 0 Name7 55 I moved my data to the first rows on my spreadsheet because I did not know how to delete just cells rather than the entire row. So if I wanted to delete both the name and cell where the cell was = 0, what is the command to do so which would fit into my code shown below? "JLGWhiz" wrote: Hi Barb, I was just answering the OPs question of why it skips. "Barb Reinhardt" wrote: That's why I put the deleted cells in a new range and then delete them when I'm done (if there's anything to delete) Barb Reinhardt "JLGWhiz" wrote: Hi Walter, just to footnote Don's remarks, when deleting entire rows, the code should start at the bottom of the used range to avoid skipping rows. The default shift after deleting a row is up. So if you have two consecutive rows that meet the delete criteria and you are progressing from top to bottom, it will miss that second row because after the delete, the second row is now the row with focus and the "Next" operator will select what was the third row, thereby skipping the second row which also contained the delete criteria. Even if you are using the Offset(1, 0) method in a Do...Loop, the effect is the same as it works downward. But when you work upward, the focus remains on the row beneath the next row to be evaluated. That is why Don's code works from the higher row number to the lower with Step -1. "Walter" wrote: Here is my coding and when it goes through the loop it makes it through the entire list of numbers but always misses the last zero which is what I am trying to delete. Any ideas as to why this is happening? The msgbox is in there just so I could see what was going on in my code. Sub CopyTransferData() ' ' CopyTransferData Macro ' Copy totals and eliminate zeros, move to Data tab. ' Dim rngCurrent As Range Dim rngCell As Range Dim result As Integer Dim shtRorkERP As Worksheet Set shtRorkERP = Application.ActiveWorkbook.Worksheets("Rork_ERP") ' ' Range("FirstIteration").Select Selection.Copy Range("C1").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Application.CutCopyMode = False Set rngCurrent = shtRorkERP.Range("C1").CurrentRegion Set rngCurrent = rngCurrent.Offset(rowoffset:=0, columnoffset:=1) Set rngCurrent = rngCurrent.Resize(columnsize:=1) For Each rngCell In rngCurrent result = rngCell.Value If rngCell.Value = 0 Then rngCell.EntireRow.Select rngCell.EntireRow.Delete End If MsgBox result Next rngCell End Sub |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop Skips Data - Why?
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
TAB to enter data, it skips one or more cells | Excel Worksheet Functions | |||
Data Validation skips rows | Setting up and Configuration of Excel | |||
code skips blanks in data | Excel Programming | |||
when I enter data in my worksheet, it skips to much lower boxes | Charts and Charting in Excel | |||
constructing a copy-paste loop that skips rows | Excel Programming |