Macro to filter one row at a time consecutively

[David28]

I want to create a macro where each time I click the macro button I place in row 1 it filters to only display one row at a time under row 1. So for example when clicked the first time only rows 1 and 2 would be visible, when clicked again only rows 1 and 3 would be visible, etc. How would I create this macro? Thanks.

 

[James006]

Hi,

A couple of event macros ...

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim Onecell As Range
Dim counter As Long
Dim rng As Range


Set rng = Range("A2:A10")
' Cell M1 keeps track of your counter
counter = [M1] + 1
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
    For Each Onecell In rng
        rng.EntireRow.Hidden = True
        If Onecell.Row = counter + 1 Then Onecell.EntireRow.Hidden = False: Exit For
    Next Onecell
[M1] = counter
Cancel = True
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub




Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
' To Unhide Rows
  Cells.EntireRow.Hidden = False
  [M1] = 0
  Cancel = True
End Sub

Hope this will help

 

[DanteAmor]

The next macro needs a cell in row 1 to perform the calculations of the row to be filtered. Please change "Z" for any available column you have.

Sub Macro7()
    Dim lr As Long, r As Variant, col As String
    Application.ScreenUpdating = False
    col = "Z"
    Cells.EntireRow.Hidden = False
    If ActiveSheet.AutoFilterMode Then ActiveSheet.AutoFilterMode = False
    lr = Cells.Find("*", Cells(1), , , xlByRows, xlPrevious, , , False).Row
    r = Cells(1, col).Value
    If r = "" Or r < 2 Or Not IsNumeric(r) Then r = 2
    Rows("2:" & lr).EntireRow.Hidden = True
    Rows(r).EntireRow.Hidden = False
    Cells(1, col).Value = r + 1
    If Cells(1, col).Value > lr Then Cells(1, col).Value = 2
End Sub

 

[Fluff]

Another option

Sub David28()
   Static Rw As Long
   Rows("2:" & Rows.Count).Hidden = True
   If Rw = 0 Then Rw = 2
   Rows(Rw).Hidden = False
   If Cells(Rw + 1, 1) = "" Then Rw = 2 Else Rw = Rw + 1
End Sub

This assumes that col A will always have a value

 

[David28]

Thanks everyone! This is exactly what I needed.

 

[Fluff]

Glad we could help & thanks for the feedback

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.