Consulting

Results 1 to 3 of 3

Thread: delete blank cells in every row individually

  1. #1
    VBAX Newbie
    Joined
    Apr 2021
    Posts
    1
    Location

    delete blank cells in every row individually

    Hi all,
    I have been trying to automate the following task with the macro recorder. I did it for one row, now I need to do that for every row individually through a range iteration. Let's say my range is A2:J23.
    Could you help me?
    Thanks

    Sub Macro1()'
    ' Macro1 Macro
    '
    
    
    '
        Range("D756").Select
        Range(Selection, Selection.End(xlToRight)).Select
        Range("D756:U756").Select
        Selection.Delete Shift:=xlToLeft
        Range("D756").Select
        Range(Selection, Selection.End(xlToRight)).Select
        Range("D756:AH756").Select
        Selection.Delete Shift:=xlToLeft
        
        
    End Sub

  2. #2
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    Start at bottom, work right to left, then up. Test on a Copy first
    Sub MessedUp()
    Dim i as long
    Application.ScreenUpdating = False
    With Range("A2:J23")
           For i = .Count to 1 Step -1
                 If .Cells(i) = "" Then .cells(i).Delete Shift:=xlToLeft
       Next i
    End With
    Application.ScreenUpdating = True
    End Sub
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  3. #3
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    Select the range you want to delete blanks from and run this:
    Sub blah()
        Selection.SpecialCells(xlCellTypeBlanks).Delete Shift:=xlToLeft
    End Sub
    If you know the range you want to delete blanks from then the likes of:
    Sub blah()
        Range("A2:J23").SpecialCells(xlCellTypeBlanks).Delete Shift:=xlToLeft
    End Sub
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

Posting Permissions

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