Quick copying Macro

[SteveChubb]

Hi Everyone,

Is there a small macro that can be done that reads down a column on a spreadsheet until it finds text in a cell and then copies that text down until it finds another cell containig text and then copies that down until it finds another call containing text and so on

I think I need to use Selection.Autofill but don't know the rest

Many thanks
SteveChubb

 

[Brianwarnock]

The macro will be something like this

Public Sub copydowndata()
 Worksheets("sheet1").Columns("B:B").Select
 newvalue = Range("B1").Value
For Each c In Selection
If c.Value = "" Or c.Value = " " Then
c.Value = newvalue
Else
newvalue = c.Value
End If
Next c

End Sub

but how you select your range is upto you, perhaps Activewindow.rangeselection

Brian

 

[SteveChubb]

Thank you Brian this is exactly what is was after

 

[HaHoBe]

Hi, Brian,

no need to select a Column or work through the whole lot of rows on it:

Dim newValue As String
Dim c As Range
newValue = Range("B1").Value
For Each c In Worksheets("sheet1").UsedRange.Columns("B:B")

I wonder if the following code wouldn´t do the trick as well (could be done as well without macro - F5/contents/Empty cells, fill in Formula to cell above highlighted cell, press CTRL+ENTER, copy whole column and pastespecial):

Sub Brian()
On Error Resume Next
With Worksheets("sheet1").UsedRange.Columns("B:B")
    .SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
    .Value = .Value
End With
End Sub

Ciao,
Holger

 

[Brianwarnock]

Hi, Brian,

no need to select a Column or work through the whole lot of rows on it:

Ciao,
Holger

Hi Holger
It was an old macro I had from long ago, but you are correct to point out the issue of selection, I believe it slows the process. I should have rewritten the macro.

I'll take that as a slap on the wrist.

Brian

 

[HaHoBe]

Hi, Brian,

no problem.

There are 2 things to my opinion which restrict the usage of the macro. It will not take care of the actual amount of used data (rows) in the sheet and will fill all rows with data (97-2003: 2^16 rows, 2007: 2^20 rows) but maybe that´s what OP wanted. AFAIK and more restricting is that selection restricts you to the active sheet while changing the code and the association with the value in Cell B1 should enable you to run the macro from any sheet without activating the sheet where the code should do it´s work.

Avoiding ScreenUpdating would speed the macro as well as putting CalculationMode to manual and disabling events (and putting them on at the end of the macro or if any error occurred).

Ciao,
Holger

 

[georgedwilkinson]

I'll take that as a slap on the wrist.

I can't imagine why. You told the OP what he asked for. Unless he says otherwise, I would think we could give him the benefit of the doubt that he will adapt it to his needs or come back for a follow-up.