macro relative position

Tody03

New Member
Joined
Jun 21, 2014
Messages
48
I have data that looks like that:
a1
b5
c3
d7
e2
f2
g
h
i
j


<colgroup><col span="2"></colgroup><tbody>
</tbody>
I want to fill the missing data in column b. that will be paralel to column a.
I wrote the attached macro
Range("B1").Select
Selection.End(xlDown).Select
Range("B7").Select
ActiveCell.FormulaR1C1 = "0"
Range("B8").Select
ActiveCell.FormulaR1C1 = "=R[-1]C"
Range("B8").Select
Selection.AutoFill Destination:=Range("B8:B16")
Range("B8:B16").Select
End Sub

my problem is that this macro does not work properly if column b is shorter or longer. How can i correct the macro that regardless the column length it will fill he missing data.
Thanks
 

Some videos you may like

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
42,956
Office Version
365
Platform
Windows
How about
Code:
Sub Tody03()
   With Range("B:B")
      .SpecialCells(xlBlanks)(1).Value = 0
      .SpecialCells(xlBlanks).FormulaR1C1 = "=r[-1]c"
   End With
End Sub
 

Tody03

New Member
Joined
Jun 21, 2014
Messages
48
How about
Code:
Sub Tody03()
   With Range("B:B")
      .SpecialCells(xlBlanks)(1).Value = 0
      .SpecialCells(xlBlanks).FormulaR1C1 = "=r[-1]c"
   End With
End Sub

It is so simple but working Thanks
Tody
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
42,956
Office Version
365
Platform
Windows
You're welcome & thanks for the feedback
 

Watch MrExcel Video

Forum statistics

Threads
1,102,891
Messages
5,489,556
Members
407,700
Latest member
SimpleJuan

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top