+ Reply to Thread
Results 1 to 4 of 4

Cell Deleting and Shifting

  1. #1
    bodhisatvaofboogie
    Guest

    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. #2
    Die_Another_Day
    Guest

    Re: 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. #3
    bodhisatvaofboogie
    Guest

    Re: 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. #4
    Die_Another_Day
    Guest

    Re: 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 to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1