Macro for visible data in filtered column RRS feed

  • Question

  • Hello Experts,

    I have been working on this part of my macro all week and I am at wits end (and googled out!)

    Sorry I have no code to post but I have tried so many variations I am asking for anything to get me in the right direction.

    I have a worksheet that at this point of the macro I filter column A for duplicates (Conditional Formatting).  If the filter return any duplicates I need my macro to continue forward from there, but if no duplicates are found I need to skip a part of the macro and move forward from that new point. 

    I have tried checking for hidden rows and visible rows and if the visible rows are blank or have data but I only seem to get it to work for 1 but not both of the conditions (dupes found or no dupes found) 

    Any help would be extremely appreciated since like I said I have tried all this part of my macro to work.

    Friday, February 19, 2016 3:19 PM

All replies

  • HI James, 

    did you mean u need a code to go next row from a filtered range? 

    if it is the question please try below.

                With ActiveSheet.AutoFilter.Range
                     Range("A" & .Offset(1, 0).SpecialCells(xlCellTypeVisible)(1).Row).Select
                End With

    Thank you

    Friday, February 19, 2016 4:18 PM
  • You need 2 loop. That is how I remove my duplications. I am using 2 columns.

    First loop

    Public Sub RunRemoveProduction()
      Dim rng1 As Range
      'Set the activecell to Range
      Set rng1 = ActiveCell
      Dim NumRowa As Integer
          ' Set numrows = number of rows of data.
      NumRows = Range("E2", Range("E2").End(xlDown)).Rows.Count
      'Check if the cell is not empty
      Do While Not (IsEmpty(ActiveCell.Value))
        Call RemoveProduction(ActiveCell.Value, CInt(NumRows))
        'Move on cell down
        rng1.Offset(1, 0).Select
        'reassign ActiveCell to Range
        Set rng1 = ActiveCell
    End Sub
    Second to remove
    Public Sub RemoveProduction(str As String, NumRows As Integer)
        'Range("L9").Value = "Work"
      Dim rng As Range
      'rng = MyValue
      Set rng = ActiveCell
      For x = 1 To NumRows
        If (InStr(UCase(ActiveCell.Value), UCase(str)) > 0) Then
          Debug.Print (ActiveCell.Value)
            ActiveCell.Value = ""
        End If
        ActiveCell.Offset(1, 0).Select
      Next x
    End Sub


    Please mark as answer if the idea helps


    Saturday, February 20, 2016 12:36 AM