CODEWITHSUNDEEP
vbaexcel
sc1324
sc1324

Reputation: 600

delete columns based on cell value vba

I have a list of product details in excel, headers in row 2, products details from row 3.

In column C, I have status of either Open or Closed and I want vba codes that can delete the whole range if the list is Open only, hence, no Closed if found. If data has both Closed and Open or just Closed, I don't have to do anything, just leave the data as it is.

This is part of the larger codes I have already written, so that is why I am hoping to achieve this using vba codes.

I am not sure if I need to set my range to column C and how to interpret rng.Cells(q, 1).Value. Right now it looks like my codes just step through and no error but nothing happens. I have provided pic of my test data and results.

Sub test()


    Dim Satus As Worksheet
    Dim LR1, q As Long
    Dim rng As Range


    Set Status = Worksheets("Sheet1")
    LR1 = Status.Cells(Rows.Count, "B").End(xlUp).Row
    Set rng = Status.Range("B2:G" & LR1)


    For q = 3 To LR1
        If InStr(1, rng.Cells(q, 1).Value, "Closed") = 0 Then
             Else
       Status.Columns("B:G").EntireColumn.Delete
       Status.Range("B2").Value = "No Closed Status"
        End If
    Next q
    End Sub

enter image description here

enter image description here

Upvotes: 0

Views: 3037

Answers (3)

Gerald Käferle
Gerald Käferle

Reputation: 1

I think this should solve your problem. You can't decide in the for loop a state for a whole column. You have to collect all single states and execute a change afterwards.

Sub test()

Dim Satus As Worksheet
Dim LR1, row As Long
Dim rng As Range
'Dim lOpen As Long
Dim lClosed As Long

Set Status = ThisWorkbook.ActiveSheet
LR1 = Status.Cells(Rows.Count, "B").End(xlUp).row
Set rng = Status.Range("B2:G" & LR1)

rngStart = 2                ' because of header line
rngEnd = rng.Rows.Count - 1 ' likewise
For row = rngStart To rngEnd
    Select Case rng.Cells(row, 2).Value
        'Case "Open"        ' just in case for future use
        '    lOpend = lOpend + 1
        Case "Closed"
            lClosed = lClosed + 1
        Case Else
     End Select
Next row

If lClosed = 0 Then
  rng.EntireColumn.Delete   ' delete just the data range
  Status.Range("B2").Value = "No Closed Status"
End If

End Sub

Upvotes: 0

Dy.Lee
Dy.Lee

Reputation: 7567

It is simple to use Worksheetfunction countif.

Sub test()
    Dim Satus As Worksheet
    Dim LR1, q As Long
    Dim rng As Range, rngDB As Range
    Dim cnt As Long

    Set Status = Worksheets("Sheet1")
    With Status
        LR1 = .Cells(Rows.Count, "B").End(xlUp).Row
        Set rng = Status.Range("B2:G" & LR1)
        Set rngDB = .Range("c3:c" & LR1)
        cnt = rngDB.Rows.Count
        If WorksheetFunction.CountIf(rngDB, "Open") = cnt Then
            rng.EntireColumn.Delete
           .Range("B2").Value = "No Closed Status"
           .Range("a1") = "Data1 Result"
        End If
    End With
End Sub

Upvotes: 0

Scott Holtzman
Scott Holtzman

Reputation: 27249

It's much simpler by directly working with objects and using Excel's native functions:

Option Explicit

Sub Test()

    Dim Status As Worksheet
    Set Status = Worksheets("Sheet1")

    With Status

        Dim LR1 As Long
        LR1 = .Range("B" & .Rows.Count).End(xlUp).Row

        If .Range("C3:C" & LR1).Find("Closed", lookat:=xlWhole) Is Nothing Then
            .Range("C3:C" & LR1).EntireRow.Delete
        End If

    End With

End Sub

Is Nothing is because .Find returns a range object if it's found. If it doesn't find it it will return, essentially, nothing.

Upvotes: 1

Related Questions