Appendix Thread. ( Codes for other Threads, ( Avinash ).)

**DocAElstein** · 09-05-2019, 10:52 AM

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

Worksheet: BasketOrder. (1)

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

Worksheet: BasketOrder. (1)

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

Worksheet: 1-Sheet1

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

Worksheet: 1-Sheet1

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