counting rows and adding cells to the bottom of the list

[Legacy 93538]

Hi

Can anyone explain how you write VBA which counts the amount of rows in a cell range and then adds values to the bottom two cells of that cell range.

So far i have the code below but it doesn't seem to do anything, it doesn't add the values to the correct cells

k = WorksheetFunction.CountA(.Range("A2:A3000"))
h = k + 2
f = k + 3

wbGCT.Sheets("Graphing").Select: Range ("C" & h).Value = "National_Average_NoSample"

wbGCT.Sheets("Graphing").Select: Range("C" & f).Value = "UpperQuartile_TRUE_NoSample"

Does anyone know how to do this?

Thanks

Jessciaseymour

 

[VoG]

Try

With wbGCT.Sheets("Graphing")
    LR = .Range("A" & Rows.Count).End(xlUp).Row
    .Range("C" & LR + 2).Value = "National_Average_NoSample"
    .Range("C" & LR + 3).Value = "UpperQuartile_TRUE_NoSample"
End With

 

[JamesW]

Hi,

Not sure what you mean by 'adds values to the bottom two cells of that cell range.' but you can use this to display the last row of your data:

Range("A" & rows.count).end(xlup).row

The above will give you the row of the last cell that has data in, however if you want to find how many rows are populated then you could use this (assuming there is no data in row 1):

    topRow = Range("A1").End(xlDown).Row
    lRow = Range("A" & Rows.Count).End(xlUp).Row
    
    numberofRows = lRow - topRow

 

[njimack]

Something like this...

Dim Last_Row As Long

With Sheets("Graphing")
Last_Row = .Range("C" & Rows.Count).End(xlUp).Row

.Cells(Last_Row+1,3) = "National_Average_NoSample"
.Cells(Last_Row+2,3) = "UpperQuartile_TRUE_NoSample"

End With

 

[Legacy 93538]

Hi

Thanks works perfectly now!!