Tests Copying, Pasting, API Cliipboard issues. and Rough notes on Advanced API stuff

[DocAElstein]

In support of this Thread:
http://www.excelfox.com/forum/showthread.php/2369-Calculation-by-vba?p=11472&viewfull=1#post11472
http://www.excelfox.com/forum/showth...ll=1#post11472

i have data upto 100 or 200 rows it can be more all it depends i have to do the same process till the end of the data

So we need to make Lr dynamic, for example
sample.xlsx
_____ Workbook: sample.xlsx ( Using Excel 2007 32 bit )

Row\Col	A	B	C	D
1	Symbol	LTP
2	ACC	1587.95	50
3	ADANIPORTS	402	70
4	AMBUJACEM	218	20
5	ASIANPAINT	1441.3	10
6	AXISBANK	733.65	5
7	BANKBARODA	115.25	7
8	BHARTIARTL	343.05	8
9	BOSCHLTD	15150	19
10	BPCL	359	350
11

Worksheet: Sheet1

'
Sub Vixer8b_MakingLrDynamic() ' http://www.excelfox.com/forum/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=11474&viewfull=1#post11474
'
Rem 1 Workbook and worksheets info
'1a) Workbook info
Dim Wbm As Workbook: Set Wbm = ThisWorkbook ' The workbook containing macro
Dim Wb1 As Workbook ' (This will be set later when the workbooks are opened)
Dim strWb1 As String: Let strWb1 = "sample.xlsx"
'1b) Worksheets info
Dim Ws1 As Worksheet ' (This 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 file   .....
 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 Lr1 As Long
 Let Lr1 = Ws1.Range("C" & Ws1.Rows.Count).End(xlUp).Row
 Let Lr1 = Ws1.Cells.Item(Ws1.Rows.Count, 3).End(xlUp).Row
 Let Lr1 = Ws1.Cells.Item(Ws1.Rows.Count, "C").End(xlUp).Row

'3b)(i) demo (i)
 Ws1.Activate
 MsgBox prompt:="Lr in worksheet " & Ws1.Name & ", in workbook " & Wb1.Name & " is   " & Lr1 & vbCrLf & "(last row in worksheet is   " & Ws1.Rows.Count & ")"

'3b)(ii) demo (ii)
 Ws1.Range("C" & Ws1.Rows.Count).Select ' select last cell in column C
 Application.Wait (Now + TimeValue("0:00:03"))  '  VBA wait 3 seconds    https://docs.microsoft.com/de-de/office/vba/api/excel.application.wait

 ActiveCell.End(xlUp).Select            ' go back up to last used cell in column C
 Application.Wait (Now + TimeValue("0:00:06"))  '  VBA wait 6 seconds

Rem 4 close file
 Wb1.Close
End Sub

---

Rem 3 making Lr dynamic
Dim Lr1 As Long
 Let Lr1 = Ws1.Range("C" & Ws1.Rows.Count).End(xlUp).Row
 Let Lr1 = Ws1.Cells.Item(Ws1.Rows.Count, 3).End(xlUp).Row
 Let Lr1 = Ws1.Cells.Item(Ws1.Rows.Count, "C").End(xlUp).Row

To explain:-

_ Rows.Count
Ws1.Range("C" & Ws1.Rows.Count)
Or
Ws1.Cells.Item(Ws1.Rows.Count, 3)
Or
Ws1.Cells.Item(Ws1.Rows.Count, "C")

We are in a .xlsx file, so Rows.Count is 1048576
Ws1.Range("C" & 1048576)
or
Ws1.Cells.Item(1048576, 3)
or
Ws1.Cells.Item(1048576, "C")

This is the last cell in column C:
Last cell in Column C in worksheet Sheet1 in workbook sample xlsx.JPG : https://imgur.com/HH9UKki
Attachment 2413
_____ Workbook: sample.xlsx ( Using Excel 2007 32 bit )

Row\Col	A	B	C	D
1048574
1048575
1048576

Worksheet: Sheet1

So we are at the bottom of the worksheet…..

_ .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) in column C from last cell in worksheet Sheet1 in workbook sample xlsx : https://imgur.com/fIDDbYB
Attachment 2411

…so we are at the last cell in column C that is filled with something

_ .Row Property
This will return the row number or the cell to which it is applied.
_Row for current active cell.JPG : https://imgur.com/uKVAIgN
Attachment 2412