how to find last non empty cell in col and use it as input in a range

[nass]

hello everyone,
i'm just starting up with VBA as a means to automate some procedures in my work environment.
i have recorded a macro in excel and i would like to generalize its functionality..
the macro is supposed to do abit of formatting and also produce a couple of other columns with computed values from the input ones.
problem is: the number of entries for each sheet that i let the macro run on differs...
so the range in which i need the computations to be carried out varies.

one solution would be to let the range of calculation be the whole column range("D1:D65535")... but i find that this is a waste of resources and time.
instead i would use some function that returns to a variable the last non empty cell of a column (as a string perhaps) and use that variable in place of "D65535"...
how do i do that however?

here is the relevant part of the code

basically, a column consists of elevations as text ie '1983 m'
and i strip the ' m' from the text. Then I propagate the formula to the following cells... but the Range("D1:D765") is not constant in all cases...


ActiveCell.FormulaR1C1 = "=LEFT(RC[-1],LEN(RC[-1])-2)"
Range("D1").Select
Selection.AutoFill Destination:=Range("D1:D765")

also , after the macro is run (even in sheets with smaller number of rows that 765) i still have to manually select the formatted columns and 'convert them to number'... from the exclamation mark icon that shows up....
how do i circumvent that?

thank you in advance for your help
nass

[mdmackillop]

Welcome to VBAX
The technique is to search up from the bottom to find the last populated cell and obtain its row number. In this case you need to look at one of your data columns, e.g. Column C
If you only need this once, you can do this as in
Option Explicit
Sub Fill_1()
Dim Rw As Long
Rw = Cells(Rows.Count, 3).End(xlUp).Row
Range("D1:D" & Rw).FormulaR1C1 = "=LEFT(RC[-1],LEN(RC[-1])-2)"
End Sub


If you may do this a few times, you can create a separate function and pass the Column as a parameter (either number or letter reference). This returns the row as a value you can use in your sub.


Option Explicit
Sub Fill_2()
Range("D1:D" & LRow("C")).FormulaR1C1 = "=LEFT(RC[-1],LEN(RC[-1])-2)"
End Sub

Function LRow(Col As Variant) As Long
LRow = Cells(Rows.Count, Col).End(xlUp).Row
End Function



As you see, the FillDown is not required.

[nass]

thank you for your quick reply... i am trying to understand the code thoroughly...:


Rw = Cells(Rows.Count, 3).End(xlUp).Row
'Rows.Count' returns the number of rows filled within a sheet?
if yes, why do I need to compute a value for 'Rw' and not use Rows.count directly?

then
Range("D1:D" & Rw)

the '& Rw' basically appends the calculated value of filled rows right?

and what about turning text into numbers..

the macro 'copies' and then 'pastes as values' thetruncated text and then i have to manually convert it to number...

[mdmackillop]

Can you post a sample workbook? Use Manage Attachments in the Go Advanced reply section. Include the code that you have.