Auto Positioning the Cursor before Executing Code
Hi There!
I've gleaned some code that copies 2 rows and inserts them below the 2nd row (2 rows below the active). However, this only works right if a user has clicked on an appropriate row. If a user has clicked somewhere else, like in the heading section, how can the cursor first be positioned in column A on the last row with data in columns A and B? Thanks! Tracy |
Auto Positioning the Cursor before Executing Code
wrote: Hi There! I've gleaned some code that copies 2 rows and inserts them below the 2nd row (2 rows below the active). However, this only works right if a user has clicked on an appropriate row. If a user has clicked somewhere else, like in the heading section, how can the cursor first be positioned in column A on the last row with data in columns A and B? Thanks! Tracy As per your information, this will select the last cell in column A where both column A and column B have data. That being said, if the last row containing data in column A is 28 and the last row containing data in column B is 15, the cell A15 will be selected, because you said the last row where A dna B contain data. Tweak as needed. Sub selectLast() Dim aRow As Long, bRow As Long, selRow As Long aRow = Cells(Rows.Count, 1).End(xlUp).Row bRow = Cells(Rows.Count, 2).End(xlUp).Row selRow = WorksheetFunction.Min(aRow, bRow) Cells(selRow, 1).Select End Sub |
Auto Positioning the Cursor before Executing Code
Also, keep in mind that a selection doesn't have to be made to
accomplish this. You just need to identify the last row and proceed acordingly. As per your information, this will select the last cell in column A where both column A and column B have data. That being said, if the last row containing data in column A is 28 and the last row containing data in column B is 15, the cell A15 will be selected, because you said the last row where A dna B contain data. Tweak as needed. This is assuming that you already have something placed on your clipboard. Sub withoutSelectLast() Dim aRow As Long, bRow As Long, selRow As Long aRow = Cells(Rows.Count, 1).End(xlUp).Row bRow = Cells(Rows.Count, 2).End(xlUp).Row selRow = WorksheetFunction.Min(aRow, bRow) Cells(selRow, 1).Offset(2, 0).Insert Shift:=xlDown End Sub wrote: Hi There! I've gleaned some code that copies 2 rows and inserts them below the 2nd row (2 rows below the active). However, this only works right if a user has clicked on an appropriate row. If a user has clicked somewhere else, like in the heading section, how can the cursor first be positioned in column A on the last row with data in columns A and B? Thanks! Tracy |
Auto Positioning the Cursor before Executing Code
Very good; thanks!
|
Auto Positioning the Cursor before Executing Code
Oops--one more thing. This needs to be restricted to row 14 or
above. Meaning, if a user has clicked somewhere above row 14, then do your code. If the user has clicked on row 15 or below, then don't do your code. :-) |
All times are GMT +1. The time now is 11:11 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com