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
1 Attachment(s)
Thoughts on adding split lines in Excel cells via the ( probably windows ) Clipboard
In support of these posts
https://stackoverflow.com/questions/...60767#54960767
https://www.eileenslounge.com/viewto...p?f=18&t=33834
Thoughts on adding split lines in Excel cells via the ( probably windows ) Clipboard
The following code snippet is typical of those which got me to the solution of how to manipulate a text string so that it pastes into Excel a cell with multiple lines.
The first section shows me what the text in "the clipboard" looks like after using Excel ways to copy my final desired test form , after using Excel ways to produce it. It gives this sort of output
"A" & vbCr & vbLf & """" & "X" & vbLf & "Y" & """" & vbCr & vbLf & "C" & vbCr & vbLf
A vbCr vbLf " X vbLf Y " vbCr vbLf C vbCr vbLf
TestvbLf_3.jpg : https://imgur.com/oPXJIkG
Attachment 2566
Either code sections 2 and 3 would error, presumably because the windows clipboard has been emptied. That is not totally understandable. We know that we had something in the windows clipboard. Doing things that empty "clipboards" , possibly other than the window clipboard, seem also somehow to remove things from the windows clipboard.
Possibly we could explain this by saying that as Excel filled the windows clipboard as a sort of extra thing to do after primarily filling its clipboard, then some linking wiring in place to do that also resulted into it clearing the windows clipboard when it cleared its clipboard
Code section 4 erroring is less understandable, as we did not use normal Excel ways to fill the window clipboard, but never the less .Clear seems to empty it.
Code section 5 erroring is similarly less understandable, since it is generally considered that Application.CutCopyMode = False clears the Excel clipboard
Before going on to sections 7 and 8, copy something to the "clipboard" from anywhere.
We find that section 7 and 8 would still error. This once again seems to be caused by either .Clear or .CutCopyMode = False. It suggests that there is some link to the windows clipboard that causes it to be cleared. It suggests perhaps that something has been set to link things in the windows clipboard from Excel or office, possibly to get some formatting parameters. If you put anything into the windows clipboard, it will still be cleared when doing .Clear or .CutCopyMode = False , by virtue of this linking "wiring"
Section 9 probably removes this linking wiring.
When a manual copy is then made in the following sections , possibly a new wiring is set up which has a different sort of dependency.
Before going on to section 10 and then again before going on to section 11, copy something to the "clipboard" from anywhere.
The code lines of .Clear or .CutCopyMode = False at the start of these sections do not remove the orange from the icon top left, and code sections 10 and 11 do not error. This supports the idea that a link was made to the windows clipboard that works slightly differently.
Code:
Sub TestvbLf_3()
ActiveSheet.Cells.Clear
Dim objDataObject As Object: Set objDataObject = GetObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
Dim StringBack As String
' Section 1
ActiveSheet.Range("A1").Value = "A"
ActiveSheet.Range("A2").Value = "X" & vbLf & "Y"
ActiveSheet.Range("A3").Value = "C"
ActiveSheet.Range("A1:A3").Copy
objDataObject.GetFromClipboard: Let StringBack = objDataObject.GetText()
Call WtchaGot(StringBack)
'ActiveSheet.Cells.Clear ' --- This clears "the clipboard",
ActiveSheet.Paste Destination:=ActiveSheet.Range("D1")
' Section 2
' ActiveSheet.Cells.Clear ' --- This clears "the clipboard"
' objDataObject.GetFromClipboard: Let StringBack = objDataObject.GetText() ' This errors
' Call WtchaGot(StringBack)
' Section 3
' Application.CutCopyMode = False
' objDataObject.GetFromClipboard: Let StringBack = objDataObject.GetText() ' This errors
' Call WtchaGot(StringBack)
' Section 4
' objDataObject.SetText "A" & vbCr & vbLf & """" & "X" & vbLf & "Y" & """" & vbCr & vbLf & "C": objDataObject.PutInClipboard
' ActiveSheet.Cells.Clear
' objDataObject.GetFromClipboard: Let StringBack = objDataObject.GetText() ' This errors
' Call WtchaGot(StringBack)
' Section 5
' objDataObject.SetText "A" & vbCr & vbLf & """" & "X" & vbLf & "Y" & """" & vbCr & vbLf & "C": objDataObject.PutInClipboard
' Application.CutCopyMode = False
' objDataObject.GetFromClipboard: Let StringBack = objDataObject.GetText()
' Call WtchaGot(StringBack)
Stop ' BEFORE DOING THE NEXT CODE SECTIONs, copy something manually via Ctrl+c ...
' Section 7
' ActiveSheet.Cells.Clear
' objDataObject.GetFromClipboard: Let StringBack = objDataObject.GetText() ' This errors
' Call WtchaGot(StringBack)
'
'' Section 8
' Application.CutCopyMode = False
' objDataObject.GetFromClipboard: Let StringBack = objDataObject.GetText() ' This errors
' Call WtchaGot(StringBack)
' Section 9
ActiveSheet.Cells.Clear ' This I think breaks the link from other clipboards to the windows clipboard.
' Application.CutCopyMode = False ' this line as alternative to the last has the same effect
Stop ' BEFORE DOING THE NEXT CODE SECTIONs, copy something manually via Ctrl+c ...
' Section 10
ActiveSheet.Cells.Clear
objDataObject.GetFromClipboard: Let StringBack = objDataObject.GetText()
Call WtchaGot(StringBack)
ActiveSheet.Paste Destination:=ActiveSheet.Range("E1")
Stop ' BEFORE DOING THE NEXT CODE SECTIONs, copy something manually via Ctrl+c ...
' Section 11
Application.CutCopyMode = False
objDataObject.GetFromClipboard: Let StringBack = objDataObject.GetText()
Call WtchaGot(StringBack)
ActiveSheet.Paste Destination:=ActiveSheet.Range("H1")
End Sub
