HELP! method 'Range' of object '_Worksheet' failure

[Sebastian K.]

Hi

I'm trying to writ a small code to determin which column has the most filled cells.

I've got the following code:

    Dim LastRow As Long
    Dim ColCount As Integer
 
        LastRow = 0
 
        For ColCount = 19 To 31 Step 4
            If Range(Cells(Rows.Count, ColCount)).End(xlUp).Row > LastRow Then _
                LastRow = Range(Cells(Rows.Count, ColCount)).End(xlUp).Row
        Next ColCount

I've tried adding Worksheets("xxxx") and Sheets("xxxx") in front of the Range but it does'nt seem to help.

Any sugestions?

Best regards
Sebastian

 

[VoG]

Perhaps

Dim LastRow As Long
Dim ColCount As Integer
 
LastRow = 0
With Sheets("xxx")
        For ColCount = 19 To 31 Step 4
            If .Range(.Cells(Rows.Count, ColCount)).End(xlUp).Row > LastRow Then _
                LastRow = .Range(Cells(Rows.Count, ColCount)).End(xlUp).Row
        Next ColCount
End With

 

[njimack]

You only need to use Range(Cells... if you're referring to more than once cell. And you can combine the comparison between the row and lastrow variable into one statement...

Dim LastRow As Long
Dim ColCount As Integer
 
        For ColCount = 19 To 31 Step 4
            LastRow = WorksheetFunction.Max(LastRow, Cells(Rows.Count, ColCount).End(xlUp).Row)
        Next ColCount

 

[nightcrawler23]

See change in red

Hi

I'm trying to writ a small code to determin which column has the most filled cells.

I've got the following code:

    Dim LastRow As Long
    Dim ColCount As Integer
 
        LastRow = 0
 
        For ColCount = 19 To 31 Step 4
            [COLOR="Red"]If Range("A" & Rows.Count).Offset(0, ColCount - 1).End(xlUp).Row > LastRow Then _
        LastRow = Range("A" & Rows.Count).Offset(0, ColCount - 1).End(xlUp).Row[/COLOR]
        Next ColCount

I've tried adding Worksheets("xxxx") and Sheets("xxxx") in front of the Range but it does'nt seem to help.

Any sugestions?

Best regards
Sebastian

 

[Sebastian K.]

You only need to use Range(Cells... if you're referring to more than once cell. And you can combine the comparison between the row and lastrow variable into one statement...

Dim LastRow As Long
Dim ColCount As Integer
 
        For ColCount = 19 To 31 Step 4
            LastRow = WorksheetFunction.Max(LastRow, Cells(Rows.Count, ColCount).End(xlUp).Row)
        Next ColCount

Thanks ..... works nicely