5 Attachment(s)
Making Lr dynamic ( using rng.End(XlUp) for a single column. )
Extra notes in support of this Thread:
http://www.excelfox.com/forum/showth...364-Delete-row
http://www.excelfox.com/forum/showth...ll=1#post11463
http://www.excelfox.com/forum/showthread.php/2364-Delete-row
Making Lr dynamic
( using rng.End(XlUp) for a single column. )
For example, from http://www.excelfox.com/forum/showth...364-Delete-row :
BasketOrder.xlsx for column C, Lr is 6
( BasketOrder.xlsx : https://app.box.com/s/v4b19po7jtjmh7wcswykbij3y896dv05
)
_____ Workbook: BasketOrder.xlsx ( Using Excel 2007 32 bit )
| Row\Col |
B |
C |
D |
|---|
| 1 |
EQ |
ACC |
NA |
| 2 |
EQ |
ADANIPORTS |
NA |
| 3 |
EQ |
AMBUJACEM |
NA |
| 4 |
EQ |
ASIANPAINT |
NA |
| 5 |
EQ |
AXISBANK |
NA |
| 6 |
EQ |
BANKBARODA |
NA |
| 7 |
|
|
|
Lr2, for column C is :
Ws2.Range("C" & Ws2.Rows.Count).End(xlUp).Row
or
Ws2.Cells.Item(Ws2.Rows.Count, 3).End(xlUp).Row
or
Ws2.Cells.Item(Ws2.Rows.Count, "C").End(xlUp).Row
To explain:
'_- 1 :- Rows.Count Property of a worksheet
Ws2.Range("C" & Ws2.Rows.Count)
or
Ws2.Cells.Item(Ws2.Rows.Count, 3)
or
Ws2.Cells.Item(Ws2.Rows.Count, "C")
For Excel 2007 and higher versions ( .xlsx .xlsm ), this is 1048576 rows in a worksheet ( ImmediateWindow RowsCount XL 2007.JPG : https://imgur.com/NHHdylV )
Ws2.Range("C" & 1048576)
or
Ws2.Cells.Item(1048576, 3)
or
Ws2.Cells.Item(1048576, "C")
This is the last cell in column C:
_____ Workbook: BasketOrder.xlsx ( Using Excel 2007 32 bit )
| Row\Col |
B |
C |
D |
|---|
| 1048574 |
|
|
|
| 1048575 |
|
|
|
| 1048576 |
|
|
|
So we are at the bottom of the worksheet….
'_- 2 :- .End(xlUp) Property action
This is the same as keyboard keys _ Ctrl+UpArrow
Ctrl + UpArrow.JPG : https://imgur.com/w5w8KxZ
….This action will take you back up to the next filled cell:
_End(XlUp).JPG : https://imgur.com/JQJxc1s
….So we are at the last filled cell in column C
'_- 3 :- .Row Property
This will give you the row number of the cell
_Row.JPG : https://imgur.com/bMpaBOv
For example, from http://www.excelfox.com/forum/showth...364-Delete-row :
1.xls for column B, Lr is 7
( 1.xls : https://app.box.com/s/beqlzzl3nwjff2ocyz4ox8twu5jnqd6e )
_____ Workbook: 1.xls ( Using Excel 2007 32 bit )
| Row\Col |
A |
B |
C |
|---|
| 1 |
Exchange |
Symbol |
Series/Expiry |
| 2 |
NSE |
ADANIPORTS |
EQ |
| 3 |
NSE |
ABC |
EQ |
| 4 |
NSE |
ASIANPAINT |
EQ |
| 5 |
NSE |
AXISBANK |
EQ |
| 6 |
NSE |
BANKBARODA |
EQ |
| 7 |
NSE |
BHARTIARTL |
EQ |
| 8 |
|
|
|
Lr1, for column B is :
Ws1.Range("B" & Ws1.Rows.Count).End(xlUp).Row
or
Ws1.Cells.Item(Ws1.Rows.Count, 2).End(xlUp).Row
or
Ws1.Cells.Item(Ws1.Rows.Count, "B").End(xlUp).Row
To explain:
'_- 1 :- Rows.Count Property of a worksheet
Ws1.Range("B" & Ws1.Rows.Count)
or
Ws1.Cells.Item(Ws1.Rows.Count, 2)
or
Ws1.Cells.Item(Ws1.Rows.Count, "B")
For Excel 97 - 2003 ( .xls ), this is 65536 rows in a worksheet ( ImmediateWindow RowsCount XL 2003.JPG : https://imgur.com/iOmrf9n )
Ws1.Range("B" & 65536 ))
or
Ws1.Cells.Item( 65536 , 2)
or
Ws1.Cells.Item( 65536 , "B")
This is the last cell in column B:
(Last Worksheet Row in XL 2003.JPG : https://imgur.com/iaEPoZG )
Attachment 2401
_____ Workbook: 1.xls ( Using Excel 2007 32 bit )
| Row\Col |
A |
B |
C |
|---|
| 65533 |
|
|
|
| 65534 |
|
|
|
| 65535 |
|
|
|
| 65536 |
|
|
|
So we are at the bottom of the worksheet …
'_- 2 :- .End(xlUp) Property action
This is the same as keyboard keys _ Ctrl+UpArrow
Ctrl + UpArrow.JPG : https://imgur.com/w5w8KxZ
Attachment 2402
…This action will take you back up to the next filled cell:
_End(XlUp) XL2003.JPG : https://imgur.com/JYPd95V
Attachment 2403
….So we are at the last filled cell in column B
'_- 3 :- .Row Property
This will give you the row number of the cell
_ Row.JPG : https://imgur.com/ZWCFvmr
Attachment 2404
Example Demo
For uploaded files..
BasketOrder.xlsx : https://app.box.com/s/v4b19po7jtjmh7wcswykbij3y896dv05
1.xls : https://app.box.com/s/beqlzzl3nwjff2ocyz4ox8twu5jnqd6e
run this macro
Code:
Sub Vixer8_MakingLrDynamic() ' http://www.excelfox.com/forum/showthread.php/2364-Delete-row?p=11463&viewfull=1#post11463
'
Rem 1 Workbook and worksheets info
'1a) Workbook info
Dim Wbm As Workbook: Set Wbm = ThisWorkbook ' The workbook containing macro
Dim Wb1 As Workbook, Wb2 As Workbook ' (These will be set later when the workbooks are opened)
Dim strWb1 As String: Let strWb1 = "1.xls" ' --- 1.xls : https://app.box.com/s/beqlzzl3nwjff2ocyz4ox8twu5jnqd6e
Dim strWb2 As String: Let strWb2 = "BasketOrder.xlsx" ' "BasketOrder..xlsx" --- BasketOrder.xlsx : https://app.box.com/s/v4b19po7jtjmh7wcswykbij3y896dv05
'1b) Worksheets info
Dim Ws1 As Worksheet, Ws2 As Worksheet ' (These will be set later when the workbooks are opened)
' Dim Lr1 As Long, Lr2 As Long ' To be determined from files : Let Lr1 = 7: Lr2 = 6 ' For sample files
Rem 2 Open files ..... we have to Open all the files all files are closed except the vba placed file
' Workbooks.Open Filename:="F:\Excel0202015Jan2016\ExcelFox\vixer\BasketOrder..xlsx"
Workbooks.Open Filename:=ThisWorkbook.Path & "\" & strWb2
Set Wb2 = ActiveWorkbook '
Set Ws2 = Wb2.Worksheets.Item(1)
' Workbooks.Open Filename:="F:\Excel0202015Jan2016\ExcelFox\vixer\1.xls"
Workbooks.Open Filename:=ThisWorkbook.Path & "\" & strWb1
Set Wb1 = ActiveWorkbook ' The workbook just opened will now be the current active workbook
Set Ws1 = Wb1.Worksheets.Item(1)
Rem 3 making Lr dynamic
Dim Lr2 As Long
Let Lr2 = Ws2.Range("C" & Ws2.Rows.Count).End(xlUp).Row
Let Lr2 = Ws2.Cells.Item(Ws2.Rows.Count, 3).End(xlUp).Row
Let Lr2 = Ws2.Cells.Item(Ws2.Rows.Count, "C").End(xlUp).Row
Dim Lr1 As Long
Let Lr1 = Ws1.Range("B" & Ws1.Rows.Count).End(xlUp).Row
Let Lr1 = Ws1.Cells.Item(Ws1.Rows.Count, 2).End(xlUp).Row
Let Lr1 = Ws1.Cells.Item(Ws1.Rows.Count, "B").End(xlUp).Row
'3b) demo
Ws2.Activate
MsgBox prompt:="Lr in worksheet " & Ws2.Name & ", in workbook " & Wb2.Name & " is " & Lr2 & vbCrLf & "(last row in worksheet is " & Ws2.Rows.Count & ")"
Ws1.Activate
MsgBox prompt:="Lr in worksheet " & Ws1.Name & ", in workbook " & Wb1.Name & " is " & Lr1 & vbCrLf & "(last row in worksheet is " & Ws1.Rows.Count & ")"
Rem 4 close files
Wb2.Close: Wb1.Close
End Sub
Ref:
https://www.excelforum.com/hello-int...ess-forum.html
http://www.excelfox.com/forum/showth...ll=1#post10192
http://www.excelfox.com/forum/showth...1466#post11466
BasketOrder.xlsx : https://app.box.com/s/v4b19po7jtjmh7wcswykbij3y896dv05
1.xls : https://app.box.com/s/beqlzzl3nwjff2ocyz4ox8twu5jnqd6e
