How can I set a variable to this cell in VBA?

[storemannequin]

I'm trying to set up this variable so I can loop in this column to find the 0's and then when found, to delete the entire row. I'm trying to set Q to equal the column header of QTY but cannot seem to get it to execute.

also, if anybody knows of a better way to do this task without looping, that's be instructive as well!

Thanks

Sub FindQTY()
With Rows("1:1")
    Set Q = Cells.Find(What:="QTY", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
        xlWhole, SearchOrder:=xlByRows).Select
 
 
 
End With
 
 
End Sub

 

[Jonmo1]

Try

Sub FindQTY()
Dim Q As Long
With Rows("1:1")
    Q = .Find(What:="QTY").Column
End With
End Sub

 

[pedie]

Are you referring to something like this?

Sub Delete_zeroValue_row()
Dim myzero As Range
    For Each myzero In Range("A1:A100")
 
    If myzero.Value = 0 Then
     myzero.EntireRow.Delete
    End If
    Next myzero
End Sub

I re-read and i think this is not related....apologies

 

[AlphaFrog]

Sub FindQTY()

    Dim Q As Range

    Set Q = Rows("1:1").Find(What:="QTY", LookIn:=xlValues, _
                            LookAt:=xlWhole, MatchCase:=True)
 
    If Not Q Is Nothing Then
        Q.EntireColumn.Select
    Else
        MsgBox "No match found."
    End If
 
End Sub

 

[storemannequin]

I'm wondering why this doesn't work then:

Dim Q As Long
FR = Cells(Rows.Count, 1).End(xlUp).Row

        With Rows("1:1")
          Q = .Find(What:="QTY").Column
          
          
          
        End With
    For k = FR To 2
        If Cells(k, Q).Value = "0" Then
            Rows(k).Delete
        End If
    Next k
    
    
End Sub

 

[AlphaFrog]

For k = FR To 2 Step -1

 

[pedie]

I tried this way and works....

[/FONT]
[FONT=Courier New]Sub TEST1()
Dim Q As Long
Dim FR As Long
Dim k As Long[/FONT]
[FONT=Courier New]    With Rows("1:1")
      Q = .Find(What:="QTY").Column
    End With[/FONT]
[FONT=Courier New]    FR = Cells(Rows.Count, Q).End(xlUp).Row
    
    For k = FR To 2 Step -1
       If Cells(k, Q).Value = "0" Then
          Cells(k, Q).EntireRow.Delete
        End If
    Next k
    
End Sub

 

[mirabeau]

I'm trying to set up this variable so I can loop in this column to find the 0's and then when found, to delete the entire row. I'm trying to set Q to equal the column header of QTY but cannot seem to get it to execute.

also, if anybody knows of a better way to do this task without looping, that's be instructive as well!

Here's some VBA code that should do as you request in bold above without looping. I hope that you find it instructive.

I must add that non-looping isn't necessarily advantageous here but, for what it's worth, this code deleted about 33,000 rows out of 100,000 with zeros in column headed QTY in under a second.

Sub delrowswithzero()
Dim lr&, lc&, x&, q, y&
lr = Cells.Find("*", after:=Cells(1), searchorder:=xlByRows, _
    searchdirection:=xlPrevious).Row
lc = Cells.Find("*", after:=Cells(1), searchorder:=xlByColumns, _
    searchdirection:=xlPrevious).Column
On Error GoTo nd
x = Application.Match("QTY", Rows("1:1"), 0)
On Error GoTo 0
Cells(1, x).Resize(lr).Name = "q"
Cells(1, lc + 1).Resize(lr) = Evaluate("if(q<>0,1,"""")")
Range("A1").Resize(lr, lc + 1).Sort Cells(1, lc + 1), 1, Header:=xlYes
y = Cells(1, lc + 1).End(4).Row
Range(Cells(y + 1, 1), Cells(lr, lc)).ClearContents
Cells(1, lc + 1).Resize(lr).ClearContents
nd:
End Sub