Results 1 to 10 of 10

Thread: Reducing a Selection by one cell.

  1. #1
    Board Regular
    Join Date
    Aug 2011
    Posts
    296
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Reducing a Selection by one cell.

    Hello,

    Today is just one of those days that Excel just keeps beating my butt. After an hour of trying to write a simple three line code and getting nowhere I have come to the experts.

    The background is that I have a string in cell A10, ten blank cells (going down the column), and then a string in cell A21.

    What I'm trying to do is Select A10 and all the blank cells in A2:A20. Do a fill down (Cntl-D) then moving to the next cell down.

    I can do this using Record Macro by.

    Cell A10 is the active cell
    Range(Selection, Selection.End(xlDown).Select
    With A10 still the active cell I hold down the Shft Key and press the End key then press the DownArrow key.
    This selects all the cells from A10 to A20. This includes A20 - which has a value already in it.
    Since I only want cells A10 through A19 selected I continue holding down Shft Key and then press the UpArrow key once.
    This leaves A10 through A19 selected.
    Now to copy the value in A10 down through A19 I press the Ctrl and DownArrow keys.
    This results in all cells between A10 and through A19 have the same value.

    All of the above steps are done in the Macro Recorder, resulting in the VBA code of:

    Range(Selection, Selection.End(xlDown)).Select
    Range("A10:A19").Select
    Application.CutCopyMode = False
    Selection.FillDown

    Since I want to do this process multiple times in the spreadsheet with different cell addresses, this code doesn't work because the UpArrow key is translated into an actual cell address.

    I've tried replacing the second line with ActiveCell.Offset(-1,0) to move up one cell. Well, it does move up one cell, but to one cell above A10, i.e. A9 and turns the selection process.

    Any help is most welcome.

    Thanks

    George Teachman

  2. #2
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    29,226
    Post Thanks / Like
    Mentioned
    483 Post(s)
    Tagged
    49 Thread(s)

    Default Re: Reducing a Selection by one cell.

    Do you want to fill all blank cells in col A with the previous value?
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  3. #3
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,975
    Post Thanks / Like
    Mentioned
    85 Post(s)
    Tagged
    15 Thread(s)

    Default Re: Reducing a Selection by one cell.

    Try this

    Change B to the column that contains more rows with data
    Code:
    Sub Macro3()
        With Range("A10:A" & Range("B" & Rows.Count).End(xlUp).Row)
          .SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
          .Value = .Value
      End With
    End Sub
    Last edited by DanteAmor; Aug 9th, 2019 at 03:17 PM.
    Regards Dante Amor

  4. #4
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    29,226
    Post Thanks / Like
    Mentioned
    483 Post(s)
    Tagged
    49 Thread(s)

    Default Re: Reducing a Selection by one cell.

    If the answer is yes try
    Code:
    Sub teachman()
       With Range("A10", Range("B" & Rows.Count).End(xlUp).Offset(, -1))
          .SpecialCells(xlBlanks).FormulaR1C1 = "=r[-1]c"
          .Value = .Value
       End With
    End Sub
    This will "fill down" to the end of column B
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  5. #5
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    35,317
    Post Thanks / Like
    Mentioned
    93 Post(s)
    Tagged
    33 Thread(s)

    Default Re: Reducing a Selection by one cell.

    Does this macro do what you want...
    Code:
    Sub FillBlanksFromAbove()
      Dim LastRow As Long
      LastRow = Cells.Find("*", , xlValues, , xlRows, xlPrevious).Row
      Application.ScreenUpdating = False
      On Error GoTo NoBlanks
      With Range("A10:A" & LastRow)
        .SpecialCells(xlBlanks).FormulaR1C1 = "=R[-1]C"
        .Value = .Value
      End With
    NoBlanks:
      Application.ScreenUpdating = True
    End Sub
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  6. #6
    Board Regular
    Join Date
    Aug 2011
    Posts
    296
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Reducing a Selection by one cell.

    Cell A10's value should be entered into cells A11 to A19.
    Cell A30's value should be entered into cells A31 to A45.
    Cell A76's value should be entered into cells A77 to A99.
    etc.

    Thanks,

    George Teachman

  7. #7
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    29,226
    Post Thanks / Like
    Mentioned
    483 Post(s)
    Tagged
    49 Thread(s)

    Default Re: Reducing a Selection by one cell.

    Are there any blank cells between A20 & A30 etc?
    If so how do you determine which blanks should be filled?
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  8. #8
    Board Regular
    Join Date
    Aug 2011
    Posts
    296
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Reducing a Selection by one cell.

    DanteAmor,

    Wow, that went through all records (> 28,000) so fast, I thought it hadn't worked. But it did.

    Mr Rothstein's suggestion also worked.
    Fluff's suggestion also worked.

    All were very fast and used slightly different approaches which just reinforces my belief that there is always more than 1 way to get the same result.

    Thank you all for your time and effort. I will be studying each suggestion.

    I've said it before, but I'll say it again. This is the best forum I've ever been involved in. You all are very patient and I've always gotten help/answers with any questions I've asked.

    Thanks,

    George Teachman

    All the other suggestions worked as well.

  9. #9
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    29,226
    Post Thanks / Like
    Mentioned
    483 Post(s)
    Tagged
    49 Thread(s)

    Default Re: Reducing a Selection by one cell.

    Glad we could help & thanks for the feedback
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  10. #10
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,975
    Post Thanks / Like
    Mentioned
    85 Post(s)
    Tagged
    15 Thread(s)

    Default Re: Reducing a Selection by one cell.

    Quote Originally Posted by teachman View Post
    DanteAmor,

    Wow, that went through all records (> 28,000) so fast, I thought it hadn't worked. But it did.


    Thanks,

    George Teachman

    All the other suggestions worked as well.

    I'm glad to help you. Thanks for the feedback.
    Regards Dante Amor

Some videos you may like

User Tag List

Tags for this Thread

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
  •