Hello all i am new to the foroums, and somewhat new to VBA the programmer at my job quit and now i need to write a VBA macro

OK so my problem is i have a list of stock tickers that gets printed out and i need to scan from starting Cell C17 in A to the bottom to where ever they end. Then take the last ticker cell and copy to C17 to a new worksheet.

I've gotten as far as being able to find the next blank space which would be the end of the tickers. I'm useing the Findnext command to find the next empty cell which takes me to the bottom, but how do you say fromthat last cell select up to C17.

Any help would be a LifeSaver

Thanks,
Keith.

One way to do it is to save the cell-address you find with FindNext (ActiveCell) and then select from C17 to that address. Add this code to the bottom of your own code:

Dim lastCell As String
lastCell = ActiveCell.Address
Range("C17:" & lastCell).Select

Guess it's a typo when you write you use FindNext. Use 'Go to..' , select 'Special' and then choose 'Last cell'. The code equivalent is:

    Selection.SpecialCells(xlCellTypeLastCell).Select

Maybe it could end up like this:

Dim LastCell As String

    Selection.SpecialCells(xlCellTypeLastCell).Select
    LastCell = ActiveCell.Address
    Range("C17:" & LastCell).Select
    Selection.Copy
    Sheets("Sheet2").Select
    Range("A1").Select
    ActiveSheet.Paste
    Range("A1").Select

Birger

Just wanted to say thanks for the help worked great.

Now i have one more little problem.
What i'm doing is copying stock data (sells) that need to be - negative. So what i'm doing is Cuting them from 1 sheet. Pasteing them to ("A:A") to another. Then i need to copy the formula in coloum B that says =sum(a1*-1). Which is fine and i can do that. But where i have a problem is finding the last Cell A that corresponds to B and filling up in the B Coloum.

I've used the Range("B1:" & lastCell).Fillup but it just makes the cell empty? I'm not sure what i'm doing wrong and i know i'm close.

Any Guidance would be Greatly appreicatated

identify the last row by going to the bottom of the spreadsheet and find the first cell with a value, Always go from bottom to top because if you go from top to bottom you may encounter a blank cell within you data and this could screw things up a bit.

    mylastrow = Range("A65536").End(xlUp).Row
    Range("B1").Copy
    Range("A1:A" & mylastrow).Select
    ActiveSheet.Paste


cheers

Matthew

It worked perfectly.

Thanks,

Just want to let you know. I'm greatful and really saved my a$$. Thanks guys.

Hi,

If you are still interested you could also use the UsedRange object. The following 2 lines of code will print the numbers of the first and last rows that contain entries to the Immediate window: obviously you want to do something else with the values but this seems to me to be the simplest way to find the last row of data on a worksheet.

  Debug.Print ActiveSheet.UsedRange.Row
  Debug.Print ActiveSheet.UsedRange.Rows.Count

Cheers
nic

Sorry,

I meant to say that you must add UsedRange.Row to UsedRange.Rows.Count to get the actual number of the last row. Obviously .Rows.Count is the number of rows actually used.

Apologies.
nic