excel smart enough to know last row?

demodren

Board Regular
Hi all, I was wondering if someone had some suggestions how to approach below problem..

I have a file with 5,000+ rows(number of rows are uneven across 3 columns.. meaning column A can have 4999 rows and column B may have 5020 rows and column C may have 3000 rows). In this case column B row 5020 would be considered my last row..

For the next step, I have a second file which contains 3 columns as well that I need to add to whatever is the last row now +1(so it doesn't overwrite last row)..(the last row will always change though on daily basis) I am just copying and pasting columns from 2nd file to whatever is the last row on my 1st spreadsheet..

Hope this makes sense..

Any advice would be greatly appreciated

Thanks so much

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
This will find the last used row in columns A:C on Sheet1. It doesn't matter which column has the last cell in the bottom row.

Code:
``````    Dim Lastrow As Long
Lastrow = Sheets("Sheet1").Range("A:C").Find("*", [A1], , , xlByRows, xlPrevious).Row``````

thanks so much AlphaFrog.. could you explain what is [A1] in the code?

also..was wondering if I can incorporate your code in below.. I currently use below code to copy/paste my columns from 2nd spreadsheet..

specifically it selects the whole column by:

Code:
``Range("F3:F" & LastRow).Select``
I define LastRow in my below code:
Code:
``LastRow = Range("A10000").End(xlUp).Row``

Can I incorporate your code below to find the last row? I am not sure if it will work with Find in it? does it need modification? this is what I use to select a column in 2nd spreadsheet, find last row and copy to 1st spreadsheet. seems like choppy way since I am going all the way to row 10,000....

Code:
``````LastRow = Range("A10000").End(xlUp).Row

'(1) Pull column1
Windows(PositionFileName2).Activate
Sheets(PositionFileName).Select
Range("F3:F" & LastRow).Select
Selection.Copy
Windows("Template.xls").Activate
Sheets("Summary").Select
Range("B3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False``````

This will do for all columns

Code:
``````With ActiveSheet.UsedRange
LastRow = .Rows(.Rows.Count).Row
End With``````

If you do a web search for Excel VBA .Find method tutorial, you'll find a lot of step-by-step help on how the .Find method works. It's quite handy. If you plan do doing more VBA coding, you'll find it well worth your time to understand it.

In this case, the .Find method is used to search for any value previous (not after) cell A1 within Columns A:C. The first cell it will find in columns A:C that is previous to cell A1 and that has a value will be the last used cell in columns A:C. That's basically how it works.

This method can be incorporated in your original code. I don't quite follow on which sheet you want to find the last used cell in columns A:C.

The commented code below is a guess to what you want.

Code:
``````    Dim wsSource As Worksheet, wsDest As Worksheet
Dim lastrow As Long, Nextrow As Long

Set wsSource = Windows(PositionFileName2).Sheets(PositionFileName)  ' The source worksheet
Set wsDest = Windows("Template.xls").Sheets("Summary")              ' The destination worksheet

' Last used row in columns A:C on the source worksheet
lastrow = wsSource.Range("A:C").Find("*", [A1], , , xlByRows, xlPrevious).Row
'(1) Pull column1
wsSource.Range("F3:F" & lastrow).Copy   'Copy column F from the source worksheet

' Paste the copied data to the next empty row on the destination worksheet
Nextrow = wsDest.Range("A:C").Find("*", , , , xlByRows, xlPrevious).Row + 1
' Past to the next empty row in column B on the destination worksheet
wsDest.Range("B" & Nextrow).PasteSpecial _
Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False``````

thanks so much for your time. I really appreciate it.. I am trying to grasp this as much as I can. I guess I should work on fixing up my initial code before moving to 2nd part which you explained below in using FIND..

Would you mind taking a look at below. I am still struggling with this Last Row i think only because I was able to find so many different solutions...

Basically I am copying all these columns(3 in this example, althought the actual spreadsheet is 15) from database which has random rows..I am defining last row as 10000. Is it better to use UsedRange or something else? Any advice is greatly appreciated.
THanks so much!

Code:
``````Sub Test()
'
'
Dim PositionFileName As String
Dim PositionFileName2 As String

'Tab name
PositionFileName = "file_" & intex_date(PrevBD(Date)) & "_NY"

PositionFileName2 = PositionFileName & ".CSV"

Dim LastRow As Long

ChDir "C:\Mymacros\Files"
Workbooks.Open fileName:= _
"C:\Mymacros\Files\" & PositionFileName

Range("A2").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Workbooks("Main_file.xls").Sheets("Mapping").Range("F2:J3"), _
Unique:=False

LastRow = Range("A10000").End(xlUp).Row

'Clear previous data
Windows("Main_file.xls").Activate
Sheets("Summary").Select
Range("A3:P" & LastRow).Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents

'(1)Pull Column 1
Windows(PositionFileName2).Activate
Sheets(PositionFileName).Select
Range("H3:H" & LastRow).Select
Selection.Copy
Windows("Main_file.xls").Activate
Sheets("Summary").Select
Range("A3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

'(2) Pull Column 2
Windows(PositionFileName2).Activate
Sheets(PositionFileName).Select
Range("BP3:BP" & LastRow).Select
Selection.Copy
Windows("Main_file.xls").Activate
Sheets("Summary").Select
Range("B3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

'(3) Pull Column 3
Windows(PositionFileName2).Activate
Sheets(PositionFileName).Select
Range("Z3:Z" & LastRow).Select
Selection.Copy
Windows("Main_file.xls").Activate
Sheets("Summary").Select
Range("C3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Workbooks(PositionFileName2).Close SaveChanges:=False

End Sub``````

Hi all, was wondering if anyone had any comments on the code I use below... the key point I am trying to accomplish is to get rid of using
LastRow = Range("A10000").End(xlUp).Row
and just find a way to find the last row in the spreadsheet even if some columns have empty rows in betweem

Any feedback is very appreciated!

Dan

I'm confused. Why isn't this working for you? It should "find the last row in the spreadsheet even if some columns have empty rows in betweem."

Lastrow = Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row

it works! great thanks so much Arch

Replies
5
Views
204
Replies
8
Views
359
Replies
1
Views
335
Replies
8
Views
458
Replies
9
Views
549

1,218,923
Messages
6,145,247
Members
450,604
Latest member
ericmwr

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back