+ Reply to Thread
Results 1 to 4 of 4

Delete certain cells if cell is blank

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    08-12-2012
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    163

    Delete certain cells if cell is blank

    i have the following code (that works!) to delete rows if the cell in column B in that row is blank. I need this changed to only delete cells in column A, B and C but not the entire row.

    so if the cell is blank in column B i only want it to delete and shift up the first three columns of that row instead of the whole row.

    heres the code
    Sub DeleteRowsInItem()
        Dim Lastrow As Integer
        
        Lastrow = Range("B" & Rows.Count).End(xlUp).Row
        
        Range("B4:B" & Lastrow).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
            
    End Sub
    thanks for any help
    JD

  2. #2
    Forum Contributor
    Join Date
    08-12-2012
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    163

    Re: Delete certain cells if cell is blank

    Correction
    I guess i didn't really explain it right in the first post

    i want the code to only delete and shift up rows in column A, B, and C only if for that row column A, B, and C are all blank

    I have data outside columns A, B, and C so this is why i can not simply delete the whole row.

    any help

  3. #3
    Forum Expert nilem's Avatar
    Join Date
    10-22-2011
    Location
    Ufa, Russia
    MS-Off Ver
    2013
    Posts
    3,377

    Re: Delete certain cells if cell is blank

    Hi JD,
    try it
    Sub DeleteRowsInItem()
    Application.ScreenUpdating = False: Columns(1).Insert
    On Error Resume Next
    With Range("A4:A" & Cells(Rows.Count, 2).End(xlUp).Row)
        .FormulaR1C1 = "=IF(COUNTBLANK(RC[1]:RC[3])=3,1,"""")"
        Intersect(Range("A:D"), .SpecialCells(-4123, 1).EntireRow).Delete
    End With
    Columns(1).Delete: Application.ScreenUpdating = True
    End Sub

  4. #4
    Forum Contributor
    Join Date
    08-12-2012
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    163

    Re: Delete certain cells if cell is blank

    Figured it out

    Sub delem()
    Dim lr As Long, r As Long
    lr = Cells(Rows.Count, "B").End(xlUp).Row
    For r = lr To 1 Step -1
        If Range("A" & r).Value = "" And Range("B" & r).Value = "" And Range("C" & r).Value = "" Then Range("A" & r & ":C" & r).Delete
    Next r
    End Sub

+ 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. [SOLVED] Macro to delete blank cells and unwanted cells in a particular column
    By Nikkishr in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 11-09-2012, 06:22 PM
  2. How to delete cells (i.e. make a blank cell) if numbers are negative
    By geomatics in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-25-2012, 11:18 AM
  3. Replies: 1
    Last Post: 05-10-2012, 04:58 PM
  4. [SOLVED] To delete blank rows having blank cell in the needed raws
    By Alexander_Golinsky in forum Excel General
    Replies: 4
    Last Post: 05-04-2012, 03:11 PM
  5. Replies: 1
    Last Post: 02-17-2012, 10:11 AM

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