Relative Reference in Excel Macro

[largeselection]

Hey again,

I'm writing a macro and I put in an autofilter because I want to delete every line with a "Q" Rank (I assign it a Q given certain requirements) So when I put autofilter I have it just show Q and then I want to delete everything below the autofilter line (where the autofilter boxes are). So i want it to delete everything with a "Q" in column D. However, when I do this in the macro it says select from line 276 down. The problem is that the first Q is not always going to start at line 276. So how do I put it in so that it deletes everything with a Q? Or subsequently, how do I put it so that it deletes everything selected below line 4 (where the autofilter is) after filtering (so only the Qs are showing).

Thanks as always

 

[lenze]

No need to filter

Sub DeleteQ()
Dim LR As Integer
LR = Range("D" & Rows.Count).End(xlUp).Row
For i = LR To 4 Step -1
If Cells(i, "D") = "Q" Then Cells(i, "D").EntireRow.Delete
Next i
End Sub

lenze

 

[Andrew Poulsom]

With a filter:

Sub Test()
    With Range("A1").CurrentRegion
        .AutoFilter
        .AutoFilter Field:=1, Criteria1:="Q"
        .Offset(1).SpecialCells(xlCellTypeVisible).EntireRow.Delete
    End With
    ActiveSheet.AutoFilterMode = False
End Sub

 

[largeselection]

Thanks to both of you for your exellent responses. Lenze I liked yours best I think.

Quick follow up- How can I use this to move parts of my column around? Here's an example:

1R
2R
3R
4R
5L
6L
7L
8T
9Q



This is how they are currently arranged in columns A, B.

I need to put all the R's in column C, all the L's in column D, all the T's in column E, and delete all the rows with Q in it.

So I figured I could just use autofilters and then just have the macro
autofilter for R > copy column B > paste into column C
autofilter for L > copy column B > paste into column D
autofilter for T > copy column B > paste into column E

then use that other bit to delete all the Qs. So can I use Andrew's to accomplish this?

 

[largeselection]

Thanks to both of you for your exellent responses. Lenze I liked yours best I think.

Quick follow up- How can I use this to move parts of my column around? Here's an example:

1R
2R
3R
4R
5L
6L
7L
8T
9Q



This is how they are currently arranged in columns A, B.

I need to put all the R's in column C, all the L's in column D, all the T's in column E, and delete all the rows with Q in it.

So I figured I could just use autofilters and then just have the macro
autofilter for R > copy column B > paste into column C
autofilter for L > copy column B > paste into column D
autofilter for T > copy column B > paste into column E

then use that other bit to delete all the Qs. So can I use Andrew's to accomplish this?

 

[lenze]

Maybe something like

Sub Fanugi()
Dim LR As Integer
Dim cl As Range
LR = Range("B" & Rows.Count).End(xlUp).Row
For i = LR To 4 Step -1
  Select Case UCase(Cells(i, "B"))
     Case "Q": Cells(i, "B").EntireRow.Delete
     Case "R": Cells(i, "C") = "R"
     Case "L": Cells(i, "D") = "L"
     Case "T": Cells(i, "E") = "T"
     Case Else:
  End Select
Next i
End Sub

HTH
lenze

 

[largeselection]

Sorry,

I was unclear when I posted. Here is a more accurate example.

1R
2R
3R
4Q
5Q
6Q
7S
8S
9T

I don't actually want to or need to move RS, or T. Rather, what I meant to say was that I need to move what is to the left of R to a new column. So instead of having 123...9 in column A. I would move 1,2,3 to column C, delete 4,5,6 (because it is Q), move 7,8 to column D, move 9 to column E.

So if there is an R in column B then copy the entries in column A and move them to C. If there is a Qin column B then delete the whole row. If there is an S in Col B then move entries in column A to D, and if there is a T in column B move the entry in column A to column E.

Sorry I put the wrong example. Thanks for all your help tho thus far.

 

[lenze]

A simple fix

Sub Fanugi()
Dim LR As Integer
Dim cl As Range
LR = Range("B" & Rows.Count).End(xlUp).Row
For i = LR To 4 Step -1
  Select Case UCase(Cells(i, "B"))
     Case "Q": Cells(i, "B").EntireRow.Delete
     Case "R": 
           Cells(i, "C") = Cells(i, "A")
           Cells(i, "A").ClearContents
     Case "L": Cells(i, "D") = Cells(i, "A")
           Cells(i, "A").ClearContents
     Case "T": Cells(i, "E") = Cells(i, "A")
           Cells(i, "A").ClearContents
     Case Else:
  End Select
Next i
End Sub

Remove the ClearConts lines if you want Column A to reain as is

lenze

 

[largeselection]

PERFECT!

That was exactly what I was looking for.

Thanks for all your help.