join column & variable to replace cell number "V65536"

d_carr

New Member
Joined
May 12, 2010
Messages
15
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)"
 

Trebor76

Well-known Member
Joined
Jul 23, 2007
Messages
4,467
Hi d_carr,

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

Code:
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:

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

Robert
 

d_carr

New Member
Joined
May 12, 2010
Messages
15
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

Well-known Member
Joined
Jul 23, 2007
Messages
4,467
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

New Member
Joined
May 12, 2010
Messages
15
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

Well-known Member
Joined
Jul 23, 2007
Messages
4,467
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

New Member
Joined
May 12, 2010
Messages
15
column A is fine to use, to find where the last row of data resides :)
 

Trebor76

Well-known Member
Joined
Jul 23, 2007
Messages
4,467
OK then, see how this goes:

Code:
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
 

Forum statistics

Threads
1,082,478
Messages
5,365,783
Members
400,850
Latest member
Raj_Jpr

Some videos you may like

This Week's Hot Topics

Top