How to find the bottom cell in a column automatically

[Colin Hayes]

Hi

I run a macro weekly to change around some data exported from Access
into a form more acceptable to another application.

The amount of rows varies with each lot of new data.

To compensate for this I have change an autofill command to accommodate
the new amount of rows.


For example , last week the command was :

Range("D1").Select
Selection.AutoFill Destination:=Range("D131439"),
Type:=xlFillDefault



There were 31439 rows in that file. In this week's file , there are
32555 rows so I will have to go through the coding to change 31439 to
32555.

What I would really like is some coding which would work out how many
rows there were in the new file and autofill to the bottom one , without
me having to change it manually every time.

Is this possible?


Thanks

Drno

 

[Don Guillett]

this should do it

x=cells(rows.count,"d").end(xlup).row
Range("D1").AutoFill Destination:=Range("D1"&x)

 

[Gord Dibben]

Colin

Try this code.

Sub Auto_Fill()
Dim lrow As Long
With ActiveSheet
lrow = Range("D" & Rows.Count).End(xlUp).Row
Range("D1" & lrow).FillDown
End With
End Sub

What the End(xlUp) does is look from the bottom of the column up until it
finds first used cell.

Another example copies row one to the first blank row below the last used row.

Sub Copyit()
Range("1:1").Copy Destination:= _
ActiveSheet.Cells(Rows.Count, 1).End(xlUp) _
.Offset(1, 0)
End Sub


Gord Dibben Excel MVP

 

[Don Guillett]

I just re-read this and am not sure that this solves your problem. It will
if you have data in col D past d1.

 

[Colin Hayes]

Colin

Try this code.

Sub Auto_Fill()
Dim lrow As Long
With ActiveSheet
lrow = Range("D" & Rows.Count).End(xlUp).Row
Range("D1" & lrow).FillDown
End With
End Sub

What the End(xlUp) does is look from the bottom of the column up until it
finds first used cell.

Another example copies row one to the first blank row below the last used row.

Sub Copyit()
Range("1:1").Copy Destination:= _
ActiveSheet.Cells(Rows.Count, 1).End(xlUp) _
.Offset(1, 0)
End Sub


Gord Dibben Excel MVP

HI

Thanks for the help - I'm grateful.

My problem of course is how to incorporate the new code into my existing
code. Within the macro there are lots of autofills - maybe 50 , and to
many different columns. It would be a minefield to go through and
implement the substitute code for each. What I usually do is the run a
search and replace to change the old number of rows to the new one.

I was hoping maybe to put a sub routine at the top of the code and then
call it at the points it is needed. In this example , instead of
replacing 34139 with 32555 , I would replace it with a reference to the
bottom cell which the sub routine had already calculated. Maybe the sub
routine could generate a variable each time the macro is run which would
act as the indicator of the bottom cell.

What do you think?

It would look a little like this :

Range("D1").Select
Selection.AutoFill Destination:=Range("D1(variable generated by sub
routine)"),
Type:=xlFillDefault

This way , I could put the routine at the top and then search and
replace 34139 with the variable code.)

Once in place of course , I would never have to search and replace ever
again! This would increase my life expectancy many fold...


Best Wishes

Drno

 

[Steved]

Hello from Steved

I would like to please to utilise your code

I have a Master and 10 worksheets on the same workbook

The data from Master to the 10 worksheets is put their by
formulas.

This is what I am trying to acheive and that is at weeks
end in this case will be Saturday's, I would like to
paste, special paste, Value in the 10 workbooks, goto
master Delete, then save.

I will open it up because off a new week and put data in
the master, using your code

Sub Auto_Fill()
Dim lrow As Long
With ActiveSheet
lrow = Range("D" & Rows.Count).End(xlUp).Row
Range("D1" & lrow).FillDown
End With
End Sub

to input to sheet1 in col B

Sub Auto_Fill()
Dim lrow As Long
With ActiveSheet
lrow = Range(Sheet1!"B" & Rows.Count).End
(xlUp).Row
Range(Sheet1!"B1:B" & lrow).FillDown
End With
End Sub

I am not understanding the fundamental off you code
please help.

Thankyou.

 

[Don Guillett]

This is what Gord and I gave you. The last cell in col D is found and used.
Do you need another last row from somewhere else?

 

[Colin Hayes]

This is what Gord and I gave you. The last cell in col D is found and used.
Do you need another last row from somewhere else?

Hi

Yes I see how to apply it now. Thanks - I'm grateful for your expertise.

I'm putting this at the top of my code :

Dim lrow As Long
With ActiveSheet
lrow = Range("D" & Rows.Count).End(xlUp).Row
'Range("D1" & lrow).FillDown
End With

and then within the code , where i used to have for example


Selection.AutoFill Destination:=Range("J2:J23319")

I now have

Selection.AutoFill Destination:=Range("J2:J" & lrow)


This works fine on one of my macros and I can vary the amount of rows in
the sheet itself and the subroutine works out how many there are and
fills accordingly. Brilliant!

On another of my macros however , I've applied the same coding as above
, only for it to give the error 'Procedure too big'.

When I remove the sub routine and put row numbers back in rather than
the variable , then it works fine with no error. Curious! Why should
this be , I wonder?


Best Wishes

Drno