CODEWITHSUNDEEP
excelexcel-2010vba
hornetbzz
hornetbzz

Reputation: 9357

Delete partial rows in VBA

T'm trying to format n ranges of 4 columns like below, expanding to the right and separated by a blank column (col "E"). The range 2 starts at column "F".

range 1

A     B               C     D ...

X     Action1     X     X
        -
        -
X     Action2     X     X
X     Action3     X     X
      #N/A     #N/A     #N/A

For each range, I want to remove rows (of 4 columns) containing "-" on the second column or "#N/A" on any column of the range, expecting this result :

range 1

A     B               C     D ...

X     Action1     X     X
X     Action2     X     X
X     Action3     X     X

This is a part of a VBA macro so I won't use manual autofilters. On top, autofiltering would remove also rows from other ranges, which is not expected.

I'm trying this code at least for testing on the 1st block, even not working :

Dim Rng As Range
  Set Rng = Range("A4", "D53")
  If Not Rng(, 2).Value = "-" Then
    Rng.Delete Shift:=xlUp
  End If

edit : I guess the answer may not be far away from this but I can't manage it properly.

Lost in VBA, some help would be great, thx in advance

EDIT: if it may help someone, I ended up with this working code thx to the below hints : 

Dim iRows, iCols, NbLig, x, BlockSize, BlockOffset, MyOffsetBtwnBlocks, CountBlocks As Integer


    BlockSize = 4

    NbLig = Range("A3").SpecialCells(xlCellTypeLastCell).Row

    CountBlocks = 0

    For iCols = 2 To NbCol Step BlockSize + 1

        iRows = Range(Cells(3, iCols), Cells(NbLig, iCols + BlockSize).End(xlToLeft)).Rows.Count

        For x = iRows To 3 Step -1
            If Application.WorksheetFunction.IsNA(Cells(x, iCols + 1)) Then
                Application.Intersect(Cells(x, iCols + 1).EntireRow, _
                     Range(Cells(3, iCols), Cells(3, iCols + BlockSize)).EntireColumn).Delete

            ElseIf Application.WorksheetFunction.IsNA(Cells(x, iCols + 2)) Then
                Application.Intersect(Cells(x, iCols + 2).EntireRow, _
                     Range(Cells(3, iCols), Cells(3, iCols + BlockSize)).EntireColumn).Delete

            ElseIf Cells(x, iCols + 1).Value = "-" Then
                Application.Intersect(Cells(x, iCols + 1).EntireRow, _
                     Range(Cells(3, iCols), Cells(3, iCols + BlockSize)).EntireColumn).Delete

            End If

            CountBlocks = CountBlocks + 1

        Next x

    Next iCols

Upvotes: 0

Views: 2143

Answers (1)

Andy G
Andy G

Reputation: 19367

This should do you:

Sub RemoveX()
    Dim iRows As Integer
    Dim x As Integer

    Application.ScreenUpdating = False
    iRows = Range("A1").CurrentRegion.Rows.Count

    For x = iRows To 1 Step -1
        If Application.WorksheetFunction.IsNA(Cells(x, 2)) Then
            Application.Intersect(Cells(x, 2).EntireRow, _
                Range("A1:D1").EntireColumn).Delete
        ElseIf Cells(x, 2).Value = "-" Then
            Application.Intersect(Cells(x, 2).EntireRow, _
                Range("A1:D1").EntireColumn).Delete
        End If
    Next x
    Application.ScreenUpdating = True
End Sub

CurrentRegion is the region obtained if you click into A1 and press Ctrl-A.

If could be tidied up a little (using Range references and not using EntireRow or -Column) but it works.

Upvotes: 1

Related Questions