Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 3 of 3

Thread: Macro to select cells with text

  1. #1
    Guest

    Default

    Hello out there. Can anyone tell me what code might work for the following scenario. If we have a worksheet "A", and let's say there is data in colums A1 to C25 that we want to copy and paste to worksheet "B".

    Is there a way to do this where the code will only copy the cells as far down as the text goes? In other words at one time the Worksheet "A" may have information in cells A1 to C25, but on another day, or in another worksheet, the data may stop at row 6. So I can't really say "Copy the data from A1 to C6", because at some point it may be "A1 to C25", or any other row. Is there something that will work for this? I want it to copy the info to Worksheet B, then return to Worksheet A.

    Thanks for any help you can offer!

  2. #2
    New Member
    Join Date
    Feb 2002
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    This code should work it find the last row
    You would then need to do the following

    location = "a1:c"& lastrow
    range(location).select

    etc

    Hope this helps - code follows:

    Sub FindLastRow()
    If WorksheetFunction.CountA(Cells) > 0 Then
    'Search for any entry, by searching backwards by Rows.
    lastrow = Cells.Find(What:="*", After:=[A1], _
    SearchOrder:=xlByRows, _
    SearchDirection:=xlPrevious).Row
    End If
    EndofList = lastrow
    Range("A1").Select
    ActiveCell.Offset(EndofList, 1).Range("A1").Select

    End Sub

  3. #3
    MrExcel MVP Barrie Davidson's Avatar
    Join Date
    Feb 2002
    Location
    Winnipeg
    Posts
    2,330
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-03-06 14:05, Anonymous wrote:
    Hello out there. Can anyone tell me what code might work for the following scenario. If we have a worksheet "A", and let's say there is data in colums A1 to C25 that we want to copy and paste to worksheet "B".

    Is there a way to do this where the code will only copy the cells as far down as the text goes? In other words at one time the Worksheet "A" may have information in cells A1 to C25, but on another day, or in another worksheet, the data may stop at row 6. So I can't really say "Copy the data from A1 to C6", because at some point it may be "A1 to C25", or any other row. Is there something that will work for this? I want it to copy the info to Worksheet B, then return to Worksheet A.

    Thanks for any help you can offer!
    Something like:

    Range("A1", Range("C65536").Address).Copy _
    Destination:=Sheets("B").Range("A1")

    will copy your range and paste it on cell A1 in a worksheet named "B".

    Regards,


    Barrie Davidson

    "You're only given a little spark of madness. You mustn't lose it." - Robin Williams

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •