excel smart enough to know last row?

[demodren]

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

 

[AlphaFrog]

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.

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

 

[demodren]

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:

Range("F3:F" & LastRow).Select

I define LastRow in my below 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....

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

 

[Rasm]

This will do for all columns

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

 

[AlphaFrog]

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.

    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

 

[demodren]

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!

Sub Test()
'
'
Dim PositionFileName As String
Dim PositionFileName2 As String
 
'Tab name
PositionFileName = "file_" & intex_date(PrevBD(Date)) & "_NY"
 
'Actual SpreadSheet file
PositionFileName2 = PositionFileName & ".CSV"
 
Dim LastRow As Long
 
    ChDir "C:\Mymacros\Files"
    Workbooks.Open fileName:= _
        "C:\Mymacros\Files\" & PositionFileName
 
' Advanced Auto Filter
    Range("A2").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
    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
 
 
    Application.DisplayAlerts = False
 Workbooks(PositionFileName2).Close SaveChanges:=False
 
    End Sub

 

[demodren]

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

 

[AlphaFrog]

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

 

[demodren]

it works! great thanks so much Arch