Autofill in a filtered range

lordterrin

Board Regular
Joined
Mar 21, 2012
Messages
155
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

Code:
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

Code:
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:

Code:
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?
 

Some videos you may like

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

rjwebgraphix

Well-known Member
Joined
May 25, 2010
Messages
588
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.

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

ukmikeb

Well-known Member
Joined
Jul 10, 2009
Messages
2,757
@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 :-
Code:
LR = Range("C" & Rows.Count).End(xlUp).Row
With Range("A6:A" & LR).SpecialCells(xlCellTypeVisible)
    .Value = "Yes"
End With

hth
 

lordterrin

Board Regular
Joined
Mar 21, 2012
Messages
155
@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 :-
Code:
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

Board Regular
Joined
Mar 21, 2012
Messages
155
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...

Code:
         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

Well-known Member
Joined
Jul 10, 2009
Messages
2,757
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 :-
Code:
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
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,122,809
Messages
5,598,202
Members
414,218
Latest member
speedbit

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top