Reducing a Selection by one cell.

teachman

Active Member
Joined
Aug 31, 2011
Messages
319
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
 

Some videos you may like

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
35,542
Office Version
365
Platform
Windows
Do you want to fill all blank cells in col A with the previous value?
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
10,192
Office Version
2007
Platform
Windows
Try this

Change B to the column that contains more rows with data
Code:
Sub Macro3()
    With Range("A10:A" & Range("[B]B[/B]" & Rows.Count).End(xlUp).Row)
      .SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
      .Value = .Value
  End With
End Sub
 
Last edited:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
35,542
Office Version
365
Platform
Windows
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
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,648
Office Version
2010
Platform
Windows
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
 

teachman

Active Member
Joined
Aug 31, 2011
Messages
319
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
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
35,542
Office Version
365
Platform
Windows
Are there any blank cells between A20 & A30 etc?
If so how do you determine which blanks should be filled?
 

teachman

Active Member
Joined
Aug 31, 2011
Messages
319
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.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
35,542
Office Version
365
Platform
Windows
Glad we could help & thanks for the feedback
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
10,192
Office Version
2007
Platform
Windows
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.
 

Forum statistics

Threads
1,089,314
Messages
5,407,519
Members
403,151
Latest member
floydschoice

This Week's Hot Topics

  • help please
    SORRY NOT ANY GOOD AT EXCEL SO HELP WOULD BE MUCH APPRECIATED this formula is in a sheet called ignore...
  • two formulas needed
    Hello, I'll try my best to explain this: First formula needed in Sheet1 cell A2: If Sheet1 cell B2 = Sheet2 cell B2 then return a 1. If not then...
  • Dynamic Counts
    Good afternoon, we are tidying up some data & the data seems to be growing quicker than we are tidying it up! What we confirm (by reviewing it...
  • Help Excel formula eliminate duplicate values and keep only 2 identical rows.
    as picture below column A has a duplicate value. but the values are not the same as the rule. sometimes 4 rows, sometimes 10 rows or 7 or 9...
  • Macro Compile Error Sub or Function not defined
    Hello, I am trying to run macros from a validation list, all macros have been created and run perfectly on there own but I'm getting a compile...
  • Last row combined with Current Region VBA
    I'm generally happy finding the last row of data through something like Lastrow = Cells(Rows.Count, "D").End(xlUp) but I don't always receive data...
Top