join column & variable to replace cell number "V65536"

[d_carr]

my question:

how do i join "V" (my column) and "erow_three" (my number of rows) to replace "V65536" in the following statement:

Range("X1") = "=COUNTA(V2:V65536)"


my current code:
Dim ws_three As Worksheet
Set ws_three = Sheets("Responsiveness - operations")
Sheets("Responsiveness - operations").Select
erow_three = ws_three.Cells(2, 1).End(xlDown).Row

Range("X1") = "=COUNTA(V2:V65536)"

 

[Blade Hunter]

Range("X1") = "=COUNTA(V2:V" & rows.count & ")"

Cheers

Dan

 

[Blade Hunter]

Edit:

why even do that, just put: Range("X1") = "=COUNTA(V:V)"

 

[Trebor76]

Hi d_carr,

For your original code, you can use this...

Dim ws_three As Worksheet
    Set ws_three = Sheets("Responsiveness - operations")
    erow_three = ws_three.Cells(2, "A").End(xlDown).Row
    
    Range("X1").Formula = "=COUNTA(V2:V" & erow_three & ")"

...or more simply just use this:

Range("X1").Formula = _
        "=COUNTA(V2:V" & Sheets("Responsiveness - operations").Range("A" & Rows.Count).End(xlUp).Row & ")"

HTH

Robert

 

[d_carr]

thanks!

second question:
regarding the below line of code, instead of going down to row 65500, how would i go down to the last row with data in it, regarding statement:

Range("Z2") = "=SUMPRODUCT(--(ISNUMBER(FIND(""TRUE"",U2:U65500))),--(V2:V65500=""R""))"

 

[Trebor76]

Is there a column that can be relied upon to work out where the last row lies or will it have to be searched for across all columns?

 

[d_carr]

i suppose cell "AG1" could store "where the last row lies" --- if thats what u mean?

im looking for a solution to speed up my macro, thus i dont wanna go down to row 65500 if i dont have too.

 

[Trebor76]

if thats what u mean

No it's not unfortunately. What I meant was is there a column I can consistently check for to determine where the last row of your data resides (just like you check Column A when you set the erow_three variable) or will I have to search for that row across all columns in the active sheet?

 

[d_carr]

column A is fine to use, to find where the last row of data resides

 

[Trebor76]

OK then, see how this goes:

Dim lngLastRow As Long

    'Set the 'lngLastRow' variable based on the last row found in Column of the _
    active (current) sheet.
    lngLastRow = Cells(Rows.Count, "A").End(xlUp).Row
    
    Range("Z2").Formula = _
        "=SUMPRODUCT(--(ISNUMBER(FIND(""TRUE"",U2:U" & lngLastRow & "))),--(V2:V" & lngLastRow & "=""R""))"

Regards,

Robert