How to execute loop only on the filtered range

[abhi17188]

Hi All,

I want to filter the range on Column D and want to copy those filtered data from column D to column A in same row i.e next visible cell value of columns D should be copied to column A.

I tried to record a macro for same , but there i get the fixed value of range. Below is the code of recorded macro:

ActiveSheet.Range("$D$1:$D$11680").AutoFilter Field:=1, Criteria1:=Array( _
"EP1", "P01", "PC1", "PC2", "PC3", "PC6", "PC8", "PCH", "PCM", "PCW", "WT1"), Operator _
:=xlFilterValues

Range("D2").Select
Selection.copy
Range("A2").Select
ActiveSheet.Paste
Range("D841").Select
Application.CutCopyMode = False
Selection.copy
Range("A841").Select
ActiveSheet.Paste
Range("D1961").Select
Application.CutCopyMode = False
Selection.copy
Range("A1961").Select
ActiveSheet.Paste
Range("D3005").Select
Application.CutCopyMode = False
Selection.copy
Range("A3005").Select
ActiveSheet.Paste
Range("D4037").Select
Application.CutCopyMode = False
Selection.copy
Range("A4037").Select
ActiveSheet.Paste
Range("D5022").Select
Application.CutCopyMode = False
Selection.copy
Range("A5022").Select
ActiveSheet.Paste
Range("D5952").Select
Application.CutCopyMode = False
Selection.copy
Range("A5952").Select
ActiveSheet.Paste
Range("D7066").Select
Application.CutCopyMode = False
Selection.copy
Range("A7066").Select
ActiveSheet.Paste
Range("D8052").Select
Application.CutCopyMode = False
Selection.copy
Range("A8052").Select
ActiveSheet.Paste
Range("D9020").Select
Application.CutCopyMode = False
Selection.copy
Range("A9020").Select
ActiveSheet.Paste
Range("D10686").Select
Application.CutCopyMode = False
Selection.copy
Range("A10686").Select
ActiveSheet.Paste


Regards,

Abhishek

 

[Jeffrey Lopez]

Welcome to the Mr. Excel Message Board!

First I would use an array to do this rather than filter and copy, just me, and a personal preference.

To do this we need to set a variable for the array:

Dim strVCheck(1 To 11)

Then we need to tell Excel what each one of those 11 values are:

strVCheck(1) = "EP1"
strVCheck(2) = "P01"
strVCheck(3) = "PC2"
strVCheck(4) = "PC3"
strVCheck(5) = "PC6"
strVCheck(6) = "PC8"
strVCheck(7) = "PCH"
strVCheck(8) = "PCM"
strVCheck(9) = "PCW"
strVCheck(10) = "WT1"

Once we have that, we can just do a loop and check for each value as we go though the list:

So this is going to look in Column D:

For Each rngCell In Range("D2:D" & lngLstRow)

And will loop up to 10 times, looking for the value:

    For i = 1 To 10
        If rngCell.Value = strVCheck(i) Then

If it finds it, then Set A (-3 columns from D) to the value in D...

            rngCell.Offset(0, -3).Value = rngCell.Value

Then we just go to the next one and do it all over again...

Thats about it, I hope that makes sense, and if you have any questions, please let me know and please back up your excel workbook before running this macro!

Sub FilteredMignon()

Application.ScreenUpdating = False

Dim rngCell As Range
Dim lngLstRow As Long
Dim strVCheck(1 To 11)

strVCheck(1) = "EP1"
strVCheck(2) = "P01"
strVCheck(3) = "PC2"
strVCheck(4) = "PC3"
strVCheck(5) = "PC6"
strVCheck(6) = "PC8"
strVCheck(7) = "PCH"
strVCheck(8) = "PCM"
strVCheck(9) = "PCW"
strVCheck(10) = "WT1"

lngLstRow = ActiveSheet.UsedRange.Rows.Count

For Each rngCell In Range("D2:D" & lngLstRow)
    For i = 1 To 10
        If rngCell.Value = strVCheck(i) Then
            rngCell.Offset(0, -3).Value = rngCell.Value
            GoTo NextVCheck
        End If
    Next i
NextVCheck:
Next

Application.ScreenUpdating = True

End Sub

 

[abhi17188]

I found one more:

Sub test1()
'
' test1 Macro
'
Columns("D:D").Select
Selection.AutoFilter
ActiveSheet.Range("$D$1:$D$61").AutoFilter Field:=1, Criteria1:=Array("EP1" _
, "P01", "PC1", "PC2"), Operator:=xlFilterValues
Range("D2").Select
For Each C In Selection.SpecialCells(4).Columns
If ActiveCell.EntireRow.Hidden = False Then
ActiveCell.Offset(0, -3).Value = ActiveCell.Offset().Value
End If
ActiveCell.Offset(1, 0).Select
Next C
End Sub

Other than this i also found using an indirect function:

Range("A2").Value = "=INDIRECT(ADDRESS(ROW(),COLUMN()+3))"=INDIRECT(ADDRESS(ROW(),COLUMN()+3))"

However, i am not able to capture "INDIRECT" function in macro.

Please let me know any suggestions to use "INDIRECT" in macro.

I will try your code also and let you know if it works



Regards,

Abhi

 

[Jeffrey Lopez]

Sounds good, let me know if what I gave you works for you!