+ Reply to Thread
Results 1 to 8 of 8

Macros fill cells regardless of row number

  1. #1
    Trixie
    Guest

    Macros fill cells regardless of row number

    Would like the macro to read:
    Go to first blank cell in column, then copy the information from the row
    above into the first blank row, without specifing the row number in the
    macro, so that it remains flexible, thank you.

  2. #2
    Forum Expert
    Join Date
    01-03-2006
    Location
    Waikato, New Zealand
    MS-Off Ver
    2010 @ work & 2007 @ home
    Posts
    2,243
    Hi Trixie,

    I'm assuming you have some knowledge of using macros from the wording of your question.

    Try the below code (adapted from Bob Phillip's post, http://www.excelforum.com/showthread.php?t=500529):

    Sub CopyToFirstBlankRow()
    Dim LastRow As Long
    LastRow = ActiveSheet.Cells(rows.Count, "A").End(xlUp).Row
    ActiveSheet.Range("A" & LastRow).EntireRow.Copy ActiveSheet.Range("A" _
    & LastRow + 1)
    End Sub

    fyi, Bob didn't include "activesheet." on his post but it appears that I need it in my setup of Excel (not sure why?).

    The above copies the entire row, if you only want some info copied (eg col's A to L) try changing the copy line to something like:
    ActiveSheet.Range("A" & LastRow & ":L" & LastRow).Copy _
    ActiveSheet.Range("A" & LastRow + 1)

    hth
    Rob Brockett
    NZ
    Always learning & the best way to learn is to experience...

  3. #3
    Trixie
    Guest

    Re: Macros fill cells regardless of row number

    Thank you so much for your reply, it works very well and is much appreciated.

    "broro183" wrote:

    >
    > Hi Trixie,
    >
    > I'm assuming you have some knowledge of using macros from the wording
    > of your question.
    >
    > Try the below code (adapted from Bob Phillip's post,
    > http://www.excelforum.com/showthread.php?t=500529):
    >
    > Sub CopyToFirstBlankRow()
    > Dim LastRow As Long
    > LastRow = ActiveSheet.Cells(rows.Count, "A").End(xlUp).Row
    > ActiveSheet.Range("A" & LastRow).EntireRow.Copy ActiveSheet.Range("A"
    > _
    > & LastRow + 1)
    > End Sub
    >
    > fyi, Bob didn't include "activesheet." on his post but it appears that
    > I need it in my setup of Excel (not sure why?).
    >
    > The above copies the entire row, if you only want some info copied (eg
    > col's A to L) try changing the copy line to something like:
    > ActiveSheet.Range("A" & LastRow & ":L" & LastRow).Copy _
    > ActiveSheet.Range("A" & LastRow + 1)
    >
    > hth
    > Rob Brockett
    > NZ
    > Always learning & the best way to learn is to experience...
    >
    >
    > --
    > broro183
    > ------------------------------------------------------------------------
    > broro183's Profile: http://www.excelforum.com/member.php...o&userid=30068
    > View this thread: http://www.excelforum.com/showthread...hreadid=524172
    >
    >


  4. #4
    Forum Expert
    Join Date
    01-03-2006
    Location
    Waikato, New Zealand
    MS-Off Ver
    2010 @ work & 2007 @ home
    Posts
    2,243
    Hi Trixie,
    Thanks for the feedback, pleased I could help.

    Rob Brockett
    NZ
    Always learning & the best way to learn is to experience...

  5. #5
    Trixie
    Guest

    Re: Macros fill cells regardless of row number

    No problem thank you for answering. I am a beginner at this but am lucky
    enough to know exactly what I require. I am looking for other basic codes
    too e.g. highlight the row the cursor is in (and spell check, or find
    specific words)... Would you happen to know where I can find the basics? I
    struggle with the command - in the active cell / row. Also, the macros made
    the spreadsheet rather large is there any way I can recude the size? Thanks
    heaps.

    "broro183" wrote:

    >
    > Hi Trixie,
    > Thanks for the feedback, pleased I could help.
    >
    > Rob Brockett
    > NZ
    > Always learning & the best way to learn is to experience...
    >
    >
    > --
    > broro183
    > ------------------------------------------------------------------------
    > broro183's Profile: http://www.excelforum.com/member.php...o&userid=30068
    > View this thread: http://www.excelforum.com/showthread...hreadid=524172
    >
    >


  6. #6
    Registered User
    Join Date
    03-23-2006
    Posts
    6
    This post was EXACTLY what I was looking for.. Although I am pretty new to excel and am having some problems.

    I did it to run but it is not going to the first blank cell, is it going to the first blank cell at the bottom and copying and filling. Is there a way to change that?

    Basically what I have and need to do is..
    Row1 = "Blah"
    Then the next 500 rows under that column are empty.

    Row 501 = "SomethingElse" Then blank rows until whatever..

    I need something to fill in 2-500 with "Blah"

    Then 502-whatever with "SomethingElse"

    I hope that takes sense, from reading the post about this.. That is what it is suppose to do but I can't get it to work. I just jumps to the very bottom and starts and skips all the blank rows above it.

    Any help would be so greatly appreciated, I have been messing with this for hours and I need to get it done tonight..

  7. #7
    Forum Expert
    Join Date
    01-03-2006
    Location
    Waikato, New Zealand
    MS-Off Ver
    2010 @ work & 2007 @ home
    Posts
    2,243
    Hi
    " I am a beginner..." Me too, but I love the challenge!

    To highlight the row the cursor is in, have a look at:
    http://excelforum.com/showthread.php?t=520284

    For spellcheck/find, I'd suggest starting by recording a macro of your actions so that you have some code to modify/adapt.

    " I struggle with the command - in the active cell / row. "
    With regards to what?

    The macro below shouldn't have made much of a difference to file size. How large is "rather large"?
    Try going to the last row, selecting all the rows underneath it & then using the menus edit-delete (not the delete key as this just clears the cell contents) and repeating this with the empty columns at the right of your sheet.Also, the link below seems to have quite a few suggestions re file size:
    http://www.ozgrid.com/forum/showthread.php?p=88327

    Some other sources of info for optimising macros/worksheets:
    http://www.mvps.org/dmcritchie/excel/getstarted.htm (good for beginners)
    http://www.cpearson.com/excel/optimize.htm
    http://www.ozgrid.com/VBA/SpeedingUpVBACode.htm
    www.decisionmodels.com

    hth
    Rob Brockett
    NZ
    Always learning & the best way to learn is to experience...

    Quote Originally Posted by Trixie
    No problem thank you for answering. I am a beginner at this but am lucky
    enough to know exactly what I require. I am looking for other basic codes
    too e.g. highlight the row the cursor is in (and spell check, or find
    specific words)... Would you happen to know where I can find the basics? I
    struggle with the command - in the active cell / row. Also, the macros made
    the spreadsheet rather large is there any way I can recude the size? Thanks
    heaps.

  8. #8
    Forum Expert
    Join Date
    01-03-2006
    Location
    Waikato, New Zealand
    MS-Off Ver
    2010 @ work & 2007 @ home
    Posts
    2,243
    hi Kevinz,

    How many rows are in the spreadsheet?
    If there are not many rows & it only has to be done once the quickest approach would have been to do it manually!
    In terms of "teaching a man to fish", the manual approach could be recorded into a macro & adapted from there - as below:
    recorded code:
    ActiveCell.Select
    Selection.Copy
    Range(Selection, Selection.End(xlDown)).Select
    ActiveCell.Range("A1:A14").Select
    ActiveSheet.Paste

    This can be adapted as follows but b/c I can't tell if you want the complete row copied in rows 2 to 500 or just the info in some cells eg A2:IV500 or A2:A500 I'll show you both...

    Sub CopyingBlanksBelowPopulatedCells()
    application.screenupdating = false
    Dim bottomOfLastCopiedSection As Long
    Repeat:
    bottomOfLastCopiedSection = Selection.End(xlDown).Row
    If bottomOfLastCopiedSection = rows.Count Then
    MsgBox "all copying except last section complete. Please copy this section manually."
    application.screenupdating = true
    Exit Sub
    Else
    ActiveCell.Range("A1").Copy Range(Selection, Selection.End(xlDown).Offset(RowOffset:=-1))
    Selection.End(xlDown).Activate
    End If
    GoTo Repeat
    application.screenupdating = true
    End Sub

    Sub CopyingBlanksBelowPopulatedRows()
    application.screenupdating = false
    Dim bottomOfLastCopiedSection As Long
    Repeat:
    bottomOfLastCopiedSection = Selection.End(xlDown).Row
    If bottomOfLastCopiedSection = rows.Count Then
    MsgBox "all copying except last section complete. Please copy this section manually."
    application.screenupdating = true
    Exit Sub
    Else
    ActiveCell.Range("A1").EntireRow.Copy Range(Selection, Selection.End(xlDown).Offset(RowOffset:=-1))
    Selection.End(xlDown).Activate
    End If
    GoTo Repeat
    application.screenupdating = true
    End Sub

    There will be tidier ways of doing this but I don't know them & I can't tell from your post how to recognise the end of used area which is why I have finished it with a message to do the last section manually. (fyi, a solution will probably involve checking the intersection of the used range with the active cell).
    Edited to include "application.screenupdating = false" & the corresponding "true" - this should stop the screen flickering as the macro runs.


    Rob Brockett
    NZ
    Always learning & the best way to learn is to experience...


    Quote Originally Posted by kevinz
    This post was EXACTLY what I was looking for.. Although I am pretty new to excel and am having some problems.

    I did it to run but it is not going to the first blank cell, is it going to the first blank cell at the bottom and copying and filling. Is there a way to change that?

    Basically what I have and need to do is..
    Row1 = "Blah"
    Then the next 500 rows under that column are empty.

    Row 501 = "SomethingElse" Then blank rows until whatever..

    I need something to fill in 2-500 with "Blah"

    Then 502-whatever with "SomethingElse"

    I hope that takes sense, from reading the post about this.. That is what it is suppose to do but I can't get it to work. I just jumps to the very bottom and starts and skips all the blank rows above it.

    Any help would be so greatly appreciated, I have been messing with this for hours and I need to get it done tonight..
    Last edited by broro183; 03-24-2006 at 07:56 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1