Autofill in a filtered range

[lordterrin]

Hi There,

I am working in a filtered worksheet, and I'm having problems with the autofill command.

Column A is blank, and I need to fill it in based on how column C is filtered.

I've gotten as far as correctly filtering this and putting the word "Yes" into cell A6 (where the worksheet starts), but when I try to autofill down, I can't figure out what command will do this on a filtered range.

I can't use

Range("A6").AutoFill Range("A6:A[I][U][B]something[/B][/U][/I]")

because I don't know the absolute value to put in as it will always be variable

and I also can't use

Range("A6").AutoFill Range("A6:A" & ActiveSheet.UsedRange.Rows.Count)

because it will then fill everything, which is obviously wrong.

I tried the most logical conclusion:

Range("A6").AutoFill Range("A6:A" & ActiveSheet.[B]VisibleRange[/B].Rows.Count)

but that doesn't exist in Excel for some stupid reason, so I got an error.

How do I do what I'm trying to do?

 

[rjwebgraphix]

Looks like you're going after the last row.... In this example I used Column C's last row assuming that it is already populated with the filter, so you just need Column C's last row to determine how far you're going down with Column A.

Dim lr As Long
lr = Range("C65536").End(xlUp).Row
Range("A6").AutoFill Range("A6:A" & lr)

 

[ukmikeb]

@rjwebgraphix
No No No your solution does exactly what the OP is trying to avoid ie fill all the cells in the range whether they are visible or not.

@lordterrin
Try this :-

LR = Range("C" & Rows.Count).End(xlUp).Row
With Range("A6:A" & LR).SpecialCells(xlCellTypeVisible)
    .Value = "Yes"
End With

hth

 

[lordterrin]

@rjwebgraphix
No No No your solution does exactly what the OP is trying to avoid ie fill all the cells in the range whether they are visible or not.

@lordterrin
Try this :-

LR = Range("C" & Rows.Count).End(xlUp).Row
With Range("A6:A" & LR).SpecialCells(xlCellTypeVisible)
    .Value = "Yes"
End With

hth

Sorry for the delay on the response - long weekend.

This worked perfectly! Thank you so much.

 

[lordterrin]

Hi There,

Again, thanks so much for this. Counting the visible rows in C, then applying that to A is brilliant.

One thing though - I'm trying not just to add text, but to format that text as well. While this macro RUNS, it doesn't do anything apart from add the text, as though it's skipping over all of the "with" statements that I have here...

         LR = Range("C" & Rows.Count).End(xlUp).Row
            With Range("A6:A" & LR).SpecialCells(xlCellTypeVisible)
                .Value = "ADD"
        With Selection.Interior
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            .ThemeColor = xlThemeColorAccent1
            .TintAndShade = 0.799981688894314
        End With
        With Selection.Interior
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            .ThemeColor = xlThemeColorLight2
            .TintAndShade = 0.799981688894314
        End With
        With ActiveCell.Characters(Start:=1, Length:=0).Font
            .Name = "Calibri"
            .FontStyle = "Bold"
            .Size = 10
        End With
        With ActiveCell.Characters(Start:=1, Length:=1).Font
            .Name = "Calibri"
            .FontStyle = "Bold"
            .Size = 11
        End With
        With ActiveCell.Characters(Start:=2, Length:=2).Font
            .Name = "Calibri"
            .FontStyle = "Bold"
            .Size = 10
        End With
            End With

 

[ukmikeb]

Hi

While you are using "With Range...." you are not selecting anything! Therefore "With Selection" and "With ActiveCell" are not operative and no actions result.

This should provide your solution :-

With Range("A6:A" & LR).SpecialCells(xlCellTypeVisible)
    .Value = "ADD"
'
    .Interior.pattern = xlSolid
    .Interior.PatternColorIndex = xlAutomatic
    .Interior.ThemeColor = xlThemeColorAccent1
    .Interior.TintAndShade = 0.799981688894314
'
    .Interior.pattern = xlSolid
    .Interior.PatternColorIndex = xlAutomatic
    .Interior.ThemeColor = xlThemeColorLight2
    .Interior.TintAndShade = 0.799981688894314
 '
 .Characters(Start:=1, Length:=1).Font.Name = "Calibri"
    .Characters(Start:=1, Length:=1).Font.FontStyle = "Bold"
    .Characters(Start:=1, Length:=1).Font.Size = 10
'
.Characters(Start:=1, Length:=1).Font.Name = "Calibri"
    .Characters(Start:=1, Length:=1).Font.FontStyle = "Bold"
     .Characters(Start:=1, Length:=1).Font.Size = 11
'
    .Characters(Start:=2, Length:=2).Font.Name = "Calibri"
      .Characters(Start:=2, Length:=2).Font.FontStyle = "Bold"
      .Characters(Start:=2, Length:=2).Font.Size = 10
End With

hth