VBA Fill first row under header

Question

Pretty simple problem that is frustrating me. I have this filtered column in my worksheet where Type is the header name:

   AO
1  Type
55 blank
62 blank
63 blank
65 blank

I'm trying to fill everything past Type in this filtered column with the text 'A'

My attempt so far:

LastRow = Range("AO" & Rows.Count).End(xlUp).Row
Range("AO:AO" & LastRow).Offset(1).Formula = "A"

But I can't seem to get this variation to work. Any help is appreciated

BruceWayne · Accepted Answer · 2017-08-08 15:45:14Z

3

Try doing

Sheets("Sheet1").Range("AO2:AO" & LastRow).SpecialCells(xlCellTypeVisible).Value = "A"

After your edit, I would perhaps change the LastRow to:

LastRow = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row

Because if you use AO after the filter, as you showed, you may have tons of blanks. Then LastRow might not set properly. Using column A (which I'm assuming has the most data?) you can help prevent any errors.

edited Aug 8, 2017 at 15:45

answered Aug 8, 2017 at 15:26

BruceWayne

23.5k16 gold badges75 silver badges126 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Scott Craner Over a year ago

I think the OP only wants the visible cells to have that data.

codeninja Over a year ago

@ScottCraner Correct, I'll add that to my post to clarift

BruceWayne Over a year ago

@ScottCraner - Ah, yeah, I meant to add that. Edited.

codeninja Over a year ago

@BruceWayne I get the error of Method 'Range' of 'object_Global' failed. Any suggestions on how to fix this? Googling suggests I need to reference the sheet? But if I were to remove the 2 in AO2 it still fills the column, which confuses me

BruceWayne Over a year ago

@codeninja - Can you edit into your OP how you declared, and define, LastRow? I'm assuming it's an integer. Also, yes you should add the sheet name to the start, to be clear. Sheets("Sheet1").Range("AO2:AO" & LastRow)...

|

Collectives™ on Stack Overflow

VBA Fill first row under header

1 Answer 1

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related