Macro for Fill Down where number of rows differs and without havingto hardcode column ranges

[exwrexona]

Hi all

I am sure there is a very quick answer to my problem but it is outside
my limited knowledge of Excel Macro's.

I have a sheet that I import data to. Data is imported into the range
starting in cell A4 and across to column AZ. The number of rows
differs depending upon each export, but can be up to 50,000+

In Column BAZ I have a heap of calculations. Row 3 stores the
calculations. I currently have a very messy macro that fills down for
each and every column individually (if I select them all at once excel
has a hisssy fit) down to row 60000

e.g.

Range("BA3:BA60000").Select
Selection.FillDown

I know I can find out what is the last row with End(xlUp) etc and I
know I can put that in a variable

e.g.
Range("BA3:BA" & Range("A65536").End(xlUp).Row).Select
' Selection.FillDown

(column A will always have data in it where the remainder of the row
contains at least some info)

But how do I make it 'neat' so that anytime I add additional columns I
don't have to add a new line to the macro

In effect I want to repeat the same filldown starting at Column BA and
going through to DZ (or further if I add extra calculations - Row 1
has column titles where there is a calculation otherwise they are
blank where there is no info and therefore no fill).

Clear as mud?

Any tips would be greatfully received as I fear my brain is turning
rapidly into an excel mush

Cheers

David

 

[Per Jessen]

Hi all

I am sure there is a very quick answer to my problem but it is outside
my limited knowledge of Excel Macro's.

I have a sheet that I import data to. Data is imported into the range
starting in cell A4 and across to column AZ. The number of rows
differs depending upon each export, but can be up to 50,000+

In Column BAZ I have a heap of calculations. Row 3 stores the
calculations. I currently have a very messy macro that fills down for
each and every column individually (if I select them all at once excel
has a hisssy fit) down to row 60000

e.g.

Range("BA3:BA60000").Select
Selection.FillDown

I know I can find out what is the last row with End(xlUp) etc and I
know I can put that in a variable

e.g.
Range("BA3:BA" & Range("A65536").End(xlUp).Row).Select
' Selection.FillDown

(column A will always have data in it where the remainder of the row
contains at least some info)

But how do I make it 'neat' so that anytime I add additional columns I
don't have to add a new line to the macro

In effect I want to repeat the same filldown starting at Column BA and
going through to DZ (or further if I add extra calculations - Row 1
has column titles where there is a calculation otherwise they are
blank where there is no info and therefore no fill).

Clear as mud?

Any tips would be greatfully received as I fear my brain is turning
rapidly into an excel mush

Cheers

David

Hi David

Try to look at this.

Application.ScreenUpdating = False
LastRow = Range("A4").End(xlDown).Row
LastColumn = Range("A3").End(xlToRight).Column
Range("BA3", Cells(LastRow, LastColumn)).Select
Selection.FillDown
Application.ScreenUpdating = True

Regards,

Per

 

[exwrexona]

Hi David

Try to look at this.

Application.ScreenUpdating = False
LastRow = Range("A4").End(xlDown).Row
LastColumn = Range("A3").End(xlToRight).Column
Range("BA3", Cells(LastRow, LastColumn)).Select
Selection.FillDown
Application.ScreenUpdating = True

Regards,

Per

Per

What can I say but "You legend"

I had the rows bit ok but End(xlToRight) for the Columns was the
missing ingredient.

Thanks again.

David

 

[exwrexona]

Hi Per

I may have been too quick on my 'you legend' praise. I am trying to
run this on the next chunk of data and I get the following error:

Run-time error '1004':
Selection is too large.

which is the same reason why I initially set my original macro up to
do one column at a time.

Is there any way that I can say for each column starting at BS fill
down to the bottom row, then progress to the next column, repeat until
last column is reached?

Thanks in advance.

David