+ Reply to Thread
Results 1 to 4 of 4

Deleting a row of cells (but not the entire row)

Hybrid View

nitto619 Deleting a row of cells (but... 11-11-2021, 07:25 PM
davesexcel Re: Deleting a row of cells... 11-11-2021, 07:39 PM
mehmetcik Re: Deleting a row of cells... 11-11-2021, 07:58 PM
Sintek Re: Deleting a row of cells... 11-12-2021, 03:12 AM
  1. #1
    Registered User
    Join Date
    11-11-2021
    Location
    CA, USA
    MS-Off Ver
    Microsoft 365
    Posts
    1

    Deleting a row of cells (but not the entire row)

    Hi all, fairly new to VBA and programming

    Need some help with trying to build something where if a cell is blank then a range of cells in that row get deleted and shifted up. I can no longer use this code because I have information on the far right that could get deleted:
    Columns("K:K").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
    So now I have to go in a different direction but for some reason it deleted my entire table (A2:O41) and seems that my if statement is not written right. This is to clean up a table of information starting with 41 rows and spans out to column O. The top line is the header.
    For i = 41 To 2 Step -1
        If Cells("K" & i) = "" Then
            Range("A" & i, "O" & i).Delete
        End If
    Next i
    What am I missing? Thanks in advance!

  2. #2
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    13,524

    Re: Deleting a row of cells (but not the entire row)

    Something like
    range(cells(i,"A"),cells(i,"O"))

  3. #3
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: Deleting a row of cells (but not the entire row)

    Sub Test()
    
    'This selects all the blanks in column K
        Columns("K:K").SpecialCells(xlCellTypeBlanks).Select
        
        Flag = True
        For Each C In Selection
        If Flag = True Then
    
       'Set Rng = Columns A to O in the first row
        Set Rng = Range("A" & C.Row & ":O" & C.Row)
        Flag = False
        Else
    
        '    Set Rng = The union of Rng and Range("A" & C.Row & ":O" & C.Row)
        Set Rng = Union(Rng, Range("A" & C.Row & ":O" & C.Row))
        End If
        Next
    
        Delete all selected areas
        Rng.Delete Shift:=xlUp
    End Sub
    My General Rules if you want my help. Not aimed at any person in particular:

    1. Please Make Requests not demands, none of us get paid here.

    2. Check back on your post regularly. I will not return to a post after 4 days.
    If it is not important to you then it definitely is not important to me.

  4. #4
    Forum Guru Sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2019 | 2021
    Posts
    14,957

    Re: Deleting a row of cells (but not the entire row)

    Sub J3v16()
    With Sheets("Sheet1").ListObjects(1)
        Intersect(.DataBodyRange, .ListColumns(11).Range).SpecialCells(4).Delete
    End With
    End Sub
    Attached Files Attached Files
    Good Luck...
    I don't presume to know what I am doing, however, just like you, I too started somewhere...
    One-day, One-problem at a time!!!
    If you feel I have helped, please click on the [★ Add Reputation] to left of post window...
    Also....Add a comment if you like!!!!
    And remember...Mark Thread as Solved...
    Excel Forum Rocks!!!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 1
    Last Post: 11-07-2012, 01:50 AM
  2. Replies: 3
    Last Post: 07-11-2012, 05:13 AM
  3. VBA for deleting entire rows
    By TheTalentedMrRipley in forum Excel General
    Replies: 3
    Last Post: 11-04-2010, 07:31 PM
  4. Deleting entire row if value in column A = 0
    By pyufak in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-07-2009, 05:43 PM
  5. Deleting entire row.
    By mangesh in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 11-07-2006, 04:37 PM
  6. [SOLVED] Deleting entire rows when several cells match
    By Steve in forum Excel General
    Replies: 2
    Last Post: 12-01-2005, 07:35 PM
  7. Deleting Entire Rows
    By gavsta in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-19-2005, 12:45 AM

Tags for this Thread

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