Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cell Deleting and Shifting
Dim lastrow As Long, i As Long
Dim cell As Range lastrow = Cells(Rows.Count, "I").End(xlUp).Row For i = lastrow To 1 Step -1 Set cell = Cells(i, "I") If IsNumeric(cell) Then If cell < 7 Then cell.Offset(0, -4).Resize(1, 6).Delete End If End If Next This formula works great, THOUGH I want it to do something a little different. It deletes the appropriate cells, however I want it to shift the information in the cells to the right of the deleted cells over into their place(so shift to left). Right now it deletes everything then shifts it up. So where do I make the change? THANKS!!! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cell Deleting and Shifting
lastrow = Cells(Rows.Count, "I").End(xlUp).Row
For i = lastrow To 1 Step -1 Set cell = Cells(i, "I") If IsNumeric(cell) Then If cell < 7 Then cell.Offset(0, -4).Resize(1, 6).Delete shift:=xlToLeft End If End If Next HTH Die_Another_Day P.S. Have you ever tried using the macro recorder to figure these things out? bodhisatvaofboogie wrote: Dim lastrow As Long, i As Long Dim cell As Range lastrow = Cells(Rows.Count, "I").End(xlUp).Row For i = lastrow To 1 Step -1 Set cell = Cells(i, "I") If IsNumeric(cell) Then If cell < 7 Then cell.Offset(0, -4).Resize(1, 6).Delete End If End If Next This formula works great, THOUGH I want it to do something a little different. It deletes the appropriate cells, however I want it to shift the information in the cells to the right of the deleted cells over into their place(so shift to left). Right now it deletes everything then shifts it up. So where do I make the change? THANKS!!! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cell Deleting and Shifting
That works great, NOW: I use this formula a couple times, when it gets to
the last row of data, I want it to do the deletion without shifting the rows up, what do I have to put in to avoid that? Without the shift part, it simply delets then shifts all remaining rows up, and I don't want that. Thanks!!!! "Die_Another_Day" wrote: lastrow = Cells(Rows.Count, "I").End(xlUp).Row For i = lastrow To 1 Step -1 Set cell = Cells(i, "I") If IsNumeric(cell) Then If cell < 7 Then cell.Offset(0, -4).Resize(1, 6).Delete shift:=xlToLeft End If End If Next HTH Die_Another_Day P.S. Have you ever tried using the macro recorder to figure these things out? bodhisatvaofboogie wrote: Dim lastrow As Long, i As Long Dim cell As Range lastrow = Cells(Rows.Count, "I").End(xlUp).Row For i = lastrow To 1 Step -1 Set cell = Cells(i, "I") If IsNumeric(cell) Then If cell < 7 Then cell.Offset(0, -4).Resize(1, 6).Delete End If End If Next This formula works great, THOUGH I want it to do something a little different. It deletes the appropriate cells, however I want it to shift the information in the cells to the right of the deleted cells over into their place(so shift to left). Right now it deletes everything then shifts it up. So where do I make the change? THANKS!!! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cell Deleting and Shifting
if all you are wanting to do is clear the data then use .ClearContents
instead of .Delete otherwise I don't quite understand what you are wanting can you elaborate please? HTH Die_Another_Day |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Deleting Rows and Shifting Up - Repost | Excel Discussion (Misc queries) | |||
Deleting and shifting cells and columns | Excel Worksheet Functions | |||
Deleting cells and shifting "right" | Excel Discussion (Misc queries) | |||
Auto Update upon shifting, inserting or deleting | Excel Worksheet Functions | |||
Deleting blanks and shifting left - cont. | Excel Programming |