Solved: Autofill to just above next cell with value?

[NYCAnalyst]

Hi,

So I dump a database query into an excel file and it gives me what you see on the left side of the picture below. How can I write a VBA Macro that uses Autofill to make it look like the right side of the picture?

i36.tinypic dot com/igyxsg.jpg



The VBA Macro should be dynamic, as the query changes every day (as you can see from the pic below).

i38.tinypic dot com/nyzcsl.jpg

I'm really stuck on this. Can anyone please help? Thanks so much! (Sorry I can't post pics because I am new and have to have at least 5 posts first)

[Simon Lloyd]

There isn't a picture attached!, however a workbook would be much better!, failing that please tell us which column or what range.

[NYCAnalyst]

How's this? Pic 1 is on top, Pic 2 on bottom.

[Simon Lloyd]

That attachment does not seem to have any bearing on the question you asked nor does it seem representative, why not try to explain what it is you have, what you would like to achieve and how you would want the output and where.

[MaximS]

try this:

[VBA]
Sub autofill()
Dim i, LastRow As Long
Dim Copied As String
LastRow = Range("B65536").End(xlUp).Row
For i = 2 To LastRow
If Cells(i, 1).Value <> "" Then
Copied = Cells(i, 1).Value
Else
Cells(i, 1).Value = Copied
End If
Next i
End Sub

[/VBA]

[NYCAnalyst]

That attachment does not seem to have any bearing on the question you asked nor does it seem representative, why not try to explain what it is you have, what you would like to achieve and how you would want the output and where.

Ok here is an attachment.

Look at range A2 : C13. That's what the database dump gives me. By clicking on the salesperson (Joe, cell A2) and double clicking on that cell's fill handle (bottom right of cell), it autofills to A6. Doing the same thing will cell A7 (Chris), and A9 (John,) gives you E2 : G13, which is the end result.

Now, the database dump is different every day. A20: C35 is an example of how it can be different. That is why I can't have exact cell references in this macro.

I think the VBA code provided above only works if I have 2 salespeople? There are many times where I have multiple. Thanks so much for all of your help.

[georgiboy]

Seems to work fine for me.

[Carl A]

This should work:

[vba]Sub AutoFill()
Dim r As Range
Dim rRng As Range
Dim rng As Range

Set r = Range("B1", Range("B65536").End(xlUp))
Cells(r.Count, 1).Value = "Woman"
Range("A:A").End(xlUp).Select
Set rng = Range(Selection.Cells(1, 1), _
Cells(65536, Selection.Column).End(xlUp))

Set rRng = rng.SpecialCells(xlCellTypeBlanks)

rRng.FormulaR1C1 = "=R[-1]C" '= cell above in same column
End Sub
[/vba]

[NYCAnalyst]

This should work:

[vba]Sub AutoFill()
Dim r As Range
Dim rRng As Range
Dim rng As Range

Set r = Range("B1", Range("B65536").End(xlUp))
Cells(r.Count, 1).Value = "Woman"
Range("A:A").End(xlUp).Select
Set rng = Range(Selection.Cells(1, 1), _
Cells(65536, Selection.Column).End(xlUp))

Set rRng = rng.SpecialCells(xlCellTypeBlanks)

rRng.FormulaR1C1 = "=R[-1]C" '= cell above in same column
End Sub
[/vba]

Thanks Carl! Any idea how I could get this to work if the target cell to start autofilling wasn't A1? A13 for example?

[Carl A]

[VBA]Sub AutoFill()
Dim r As Range
Dim rRng As Range
Dim rng As Range

Set r = Range("B1", Range("B65536").End(xlUp))

Cells(r.Count, 1).Value = "Woman"

Range("A13", "A" & r.Count).Select

Set rng = Range(Selection.Cells(1, 1), _
Cells(65536, Selection.Column).End(xlUp))

Set rRng = rng.SpecialCells(xlCellTypeBlanks)
rRng.FormulaR1C1 = "=R[-1]C" '= cell above in same column
End Sub


[/VBA]

[NYCAnalyst]

[vba]Sub AutoFill()
Dim r As Range
Dim rRng As Range
Dim rng As Range

Set r = Range("B1", Range("B65536").End(xlUp))

Cells(r.Count, 1).Value = "Woman"

Range("A13", "A" & r.Count).Select

Set rng = Range(Selection.Cells(1, 1), _
Cells(65536, Selection.Column).End(xlUp))

Set rRng = rng.SpecialCells(xlCellTypeBlanks)
rRng.FormulaR1C1 = "=R[-1]C" '= cell above in same column
End Sub


[/vba]

Many thanks!