Can Excel find the last row on a PAGE?
Posted by Paul B on January 31, 2002 3:57 AM
I have a list in Excel in columns A, B ,C, D, about 250 rows, I am taking the rows at the start of page two and move them to columns F,G,H,I, leaving column E between them, to fill up the page, and then take what is left and do the same thing so it will fit on page two. I am currently using a Marco to do the cutting , pasting, deleting rows, then cutting, pasting again what is left to accomplish this, but I have to put in where the last row on the page is and where the first row on the next page will be, this changes if the margins change or the rows expand because of text wrapping, is there a way in Excel something like the way to find the last row, range(A65536).End(xlup).row, to find the last row and the first row ON A PAGE? If so how? Thanks to all who take the time to help out on this board, it is a real help to the people trying to learn Excel and VBA. I am using Excel 97
Posted by JohnG on January 31, 2002 7:46 AM
Example
Sub FindLastRow()
Dim LastRow As Integer
LastRow = Worksheets("Sheet1").Range("A65536").End(xlUp).Row
MsgBox "Last used row in Column A is " & LastRow
End Sub
Posted by Paul B on January 31, 2002 8:05 AM
I can find the last row but ,I need the last row at the end of the PAGE(NT)
Posted by Lucullus on January 31, 2002 4:36 PM
Try this ....
Sub One_Page_Columns()
'Convert columns A:D data to columns one-page long
Dim rw%, col%
rw = ExecuteExcel4Macro("INDEX(GET.DOCUMENT(64)," & 1 & ")")
col = 1
Do
Range(Cells(rw, col), Cells(65536, col).End(xlUp)).Resize(, 4).Cut Cells(1, col + 5)
col = col + 5
If Cells(rw, col).End(xlDown).Row = 65536 Then Exit Do
Loop
End Sub
Posted by Paul B on January 31, 2002 5:44 PM
Re: Try this ....
That works great to fill the first page thanks, the remaining numbers go to K1 and down, is there any way to make what is left start on page two and fill in like the first page did. Thanks again
Posted by Paul B on January 31, 2002 6:16 PM
Thanks, My mistake the ones on the right ARE the second page THANKS(NT)
Posted by Paul B on January 31, 2002 8:19 PM
One More Thing....
I added some code to get my columns adjusted before the Marco runs and it does exactly what I need it to, the only problem that I can see is if the data is less than two pages, the Marco crashes if this is true, it says Run time error 13 type mismatch this is the part of the code that is highlighted rw = ExecuteExcel4Macro("INDEX(GET.DOCUMENT(64)," & 1 & ")") is there any way to modify the Marco for this? Maybe even have a message box pop up and say the data is less than two pages. It amazes me the things that can be done with VBA, I am just starting to pick some of it up, again thanks for your help it is greatly appreciated
Posted by Lucullus on February 01, 2002 4:58 AM
Re: One More Thing....
Sub One_Page_Columns()
'Convert columns A:D data to columns one-page long
Dim rw%, col%
On Error GoTo e
rw = ExecuteExcel4Macro("INDEX(GET.DOCUMENT(64)," & 1 & ")")
col = 1
Do
Range(Cells(rw, col), Cells(65536, col).End(xlUp)).Resize(, 4).Cut Cells(1, col + 5)
col = col + 5
If Cells(rw, col).End(xlDown).Row = 65536 Then Exit Do
Loop
e:
On Error GoTo 0
End Sub
I added some code to get my columns adjusted before the Marco runs and it does exactly what I need it to, the only problem that I can see is if the data is less than two pages, the Marco crashes if this is true, it says Run time error 13 type mismatch this is the part of the code that is highlighted rw = ExecuteExcel4Macro("INDEX(GET.DOCUMENT(64)," & 1 & ")") is there any way to modify the Marco for this? Maybe even have a message box pop up and say the data is less than two pages. It amazes me the things that can be done with VBA, I am just starting to pick some of it up, again thanks for your help it is greatly appreciated
Posted by Paul B on February 01, 2002 8:23 AM
Thanks
