CODEWITHSUNDEEP
vbaexcel
Officer_Narc
Officer_Narc

Reputation: 304

How do I find the last row in a column I'm currently looping through

c specifies the columns I'm looping through and the Style is a highlight applied to blank cells. If the cell is blank I need the ID of that row (Cells(i,4)) to be copied to a reports page in the column c that I'm currently looping through. For readability I'm trying to copy each instance in the next available cell of that row but as you can imagine I'm getting an error at the Range(c & Rows.Count) portion of the code.

I'm aware that I can put A or any other column letter there but i'm just wondering if i were to be able to put the variable that I'm iterating with there instead. Any tips for this? 

For c = 1 To 103
    For i = 1 To coor(2)
        If Cells(i, c).Style = "60% - Accent2" Then
            Cells(i, 4).Copy Sheets("ReportsPage").Range(c & Rows.Count).End(xlUp).Offset(1, 0)
        End If
    Next i
Next c

Upvotes: 1

Views: 566

Answers (2)

Scott Craner
Scott Craner

Reputation: 152450

Use Cells() instead of Range(). Cells() allows for the use of cardinal location:

For c = 1 To 103
    For i = 1 To coor(2)
        If Cells(i, c).Style = "60% - Accent2" Then
            Cells(i, 4).Copy Sheets("ReportsPage").Cells(Rows.Count,c).End(xlUp).Offset(1, 0)
        End If
    Next i
Next c

One more note, one should always append any range object with their parent sheet, even if it is the activesheet:

With ActiveSheet
    For c = 1 To 103
        For i = 1 To coor(2)
            If .Cells(i, c).Style = "60% - Accent2" Then
                .Cells(i, 4).Copy Sheets("ReportsPage").Cells(Rows.Count,c).End(xlUp).Offset(1, 0)
            End If
        Next i
    Next c
End With

Upvotes: 2

Hasib_Ibradzic
Hasib_Ibradzic

Reputation: 666

You can use this to find the last column:

Dim LastCol As Integer
With ActiveSheet
    LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
End With

Upvotes: 0

Related Questions