Macro to identify Last row selects formula cell instead of cell with value

[skarnik01]

Hi,

In a macro, I am trying to identify last cell in the sheet and then auto-populating corresponding 25-30 adjacent cells based on certain calculations.
Macro used is -
LastRow2 = Range("A2").End(xlDown).Row
Range("B601:AB601").AutoFill Destination:=Range(Range("B601"), Range("AB" & LastRow2))
There are 4 dynamic charts which get auto-updated based on the values that get filled in these cells.

The problem is that the macro identifies the last cell as a cell with formula [Formula in the cell is ---- IF('Data Calculations'!B661>0,'Data Calculations'!B661,"")]. I expect a cell with a value to be identified instead of formula cell. As a result, the adjacent cells get auto-filled with #VALUE! error.

In turn the corresponding Charts drop down to zero once the macro runs against the formula cells without values.

Request you to help me know how I can identify through a macro the last cell in the column but with a value (ignoring the formulae present in that cell).

 

[Sathish KV]

Hi,

When you say last row with value, does it mean cell with value or value arrived through a formula

 

[skarnik01]

HI Sathish,

Thanks for replying. Its the cell with value arrived from formula

 

[Sathish KV]

Hi,

Then all your cells in Column A would have a formula. In this some may contain value.

What about the formulas which has no values

Will it display a blank or 0 or an error

 

[skarnik01]

Hi,

Then all your cells in Column A would have a formula. In this some may contain value.

What about the formulas which has no values

Will it display a blank or 0 or an error

Yes, all cells above the last row with values have values (no blank cells exist in between).
For cells with formulas having no values, it shows a blank.

 

[Sathish KV]

Hi,

Does the below code solve your problem

Range("A1").Select
Do Until ActiveCell.Value = ""
ActiveCell.Offset(1, 0).Select
Loop
LastRow2 = ActiveCell.Row - 1

 

[skarnik01]

Hi,

Does the below code solve your problem

Range("A1").Select
Do Until ActiveCell.Value = ""
ActiveCell.Offset(1, 0).Select
Loop
LastRow2 = ActiveCell.Row - 1

Hey Sathish, it indeed works!

Thanks a ton!

 

[ugcs93]

I swear this code was working - before I tweaked it! It's supposed to autofill on 3 columns, based on the last cell with a value in column A (I think). Column A is being populated with a formula, so maybe that won't work? I do have other columns with data, and could use one of those - I'm just not sure which is the range and which is the destination anymore. Please help -I've been at this all day:

Sub ENGLISH()
'
' ENGLISH Macro
'
' Keyboard Shortcut: Ctrl+Shift+E
'
Dim LastRow As Long

LastRow = Range("D65000").End(xlUp).Row
Columns("A:A").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("A1").Select
ActiveCell.FormulaR1C1 = "KEY"
Range("A2").Select
ActiveCell.FormulaR1C1 = "=CONCATENATE(RC[5],RC[6])"
Range("A2:A2").AutoFill Destination:=Range(Range("A2"), Range("A2" & LastRow))
Range("N2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-13],MASTERLISTDOB!R1:R1048576,6,FALSE)"
Range("N2:N2").Select
Range("N2:N2").AutoFill Destination:=Range(Range("N2"), Range("N2" & LastRow))
Range("O2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-14],MASTERLISTDOB!R1:R1048576,8,FALSE)"
Range("O2").Select
Range("O2:O2").AutoFill Destination:=Range(Range("O2"), Range("O2" & LastRow))
Range("P2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-15],MASTERLISTDOB!R1:R1048576,2,FALSE)"
Range("P2").Select
Range("P22").AutoFill Destination:=Range(Range("P2"), Range("P2" & LastRow))
Range("O1").Select
ActiveCell.FormulaR1C1 = "EXAM DATE"
Range("P1").Select
ActiveCell.FormulaR1C1 = "PER CODE"
Range("N1").Select
ActiveCell.FormulaR1C1 = "DEPT"
Range("L1").Select
ActiveCell.FormulaR1C1 = "COURSE"
Cells.Select
Cells.EntireColumn.AutoFit
Range("B2").Select
End Sub