# Autofill in a filtered range

#### lordterrin

##### Board Regular
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?

### Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
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)``````

@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

@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.

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)
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent1
End With
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorLight2
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``````

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)
'
.Interior.pattern = xlSolid
.Interior.PatternColorIndex = xlAutomatic
.Interior.ThemeColor = xlThemeColorAccent1
'
.Interior.pattern = xlSolid
.Interior.PatternColorIndex = xlAutomatic
.Interior.ThemeColor = xlThemeColorLight2
'
.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:

Replies
1
Views
329
Replies
2
Views
271
Replies
5
Views
660
Replies
13
Views
946
Replies
12
Views
861

1,219,961
Messages
6,151,171
Members
451,012
Latest member
needvbahelp1

### 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.

### Which adblocker are you using?

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

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