Solved: Row Limitations

[vzachin]

Hi,

I have a macro that basically queries a mainframe for data and copies & pastes the information back to Excel.
Knowing the row limitation in Excel,
(1) how can I have the macro stop and error out with a message when it approaches the last row
or
(2) if it reaches the last row, can I continue pasting in other columns instead? this is what i would prefer to do




Sub RowLimit()
Dim Rw As Long
Rw = 4
'This sheet contains the data which queries the mainframe
Sheets("Data").Select
For i = 5 To 65536
If Range("A" & i).FormulaR1C1 = "" Then
MsgBox "Completed"
Exit Sub
End If

'Macro then retrieves data from the mainframe
'And adds the information to Excel

'This adds the data to Excel, where i have the question about the row limits
Rw = Rw + 1
With Worksheets("OUTPUT")
.Cells(Rw, "C").Value = City
.Cells(Rw, "D").Value = County
End With

Next
End Sub



thanks
zach

[Jacob Hilderbrand]

You can check what the value of Rw is.


Sub RowLimit()

Dim Rw As Long
Dim Col As Long

Rw = 4
Col = 3

'This sheet contains the data which queries the mainframe
Sheets("Data").Select
For i = 5 To 65536
If Range("A" & i).FormulaR1C1 = "" Then
MsgBox "Completed"
Exit Sub
End If

'Macro then retrieves data from the mainframe
'And adds the information to Excel

'This adds the data to Excel, where i have the question about the row limits
Rw = Rw + 1
With Worksheets("OUTPUT")
.Cells(Rw, Col).Value = City
.Cells(Rw, Col + 1).Value = County
End With
If Rw = 65536 Then
Rw = 4
Col = Col + 1
End If

Next

End Sub

[Bob Phillips]

For future compatibility, better to replace


For i = 5 To 65536


with


For i = 5 To Activesheet.Rows.Count

[vzachin]

thanks DRJ for your help on the coding.
and thanks xld for your tip.

zach

[vzachin]

i have a question:
how would i write

If Rw = 65536 Then

to incorporate future compatabilities

For i = 5 To Activesheet.Rows.Count


zach

[Bob Phillips]

If Rw = Activesheet.Rows.Count Then

[vzachin]

never mind, i answered my own question:




If Rw = ActiveSheet.Rows.Count Then


thanks again for all the help!