populating a column with an existing cell info based on a adjacent populated column

Question

trying to populate a range, but the range destination always changes. how can i adjust the populated cell range (listed as A3:A12 below) with some kind of variable that will adjust "A12" based on the cell range of column b? such as B3-B19 so A3-A19 fills with A2 in one sheet and B3-B49 on another sheet means that B3-B49 fills with A2 on another sheet without having to change the macro everytime... thanks!

Sub Space_delimiter2col()
Application.DisplayAlerts = False
With Range(Selection.Cells, Selection.Cells.End(xlDown))
    .TextToColumns Destination:=.Cells(1, 1), _
                   DataType:=xlDelimited, _
                   Space:=True
End With
Columns("A:A").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("A2").Value = "Switch"
Range("A3:A12").Value = Range("B1").Value
Rows(1).EntireRow.Delete

End Sub

i don't understan what exactly you mean can u give more details — ExcelinEfendisi
Have you tried using a "named" range. Rows inserted or deleted will adjust the range. Is A12 the last line containing data on the sheet ? (which may vary) — Mitch
for this sheet A12 is the last, but every sheet will always be different. — sdj2879

barvobot barvobot · Accepted Answer · 2017-09-08T19:32:10

If you have a contiguous series of data in column B, you could replace Range("A3:A12").Value = Range("B1").Value with Range("A3:A" & Range("B3").End(xlDown).Row).Value = Range("B1").Value.

If column B doesn't have a contiguous series of data, and there isn't data below the end of the series you want to fill next to, you can use Range("A3:A" & Cells(Rows.Count,2).End(xlUp).Row).Value = Range("B1").Value in place of Range("A3:A12").Value = Range("B1").Value .

For reference, including sample data is always helpful: see https://stackoverflow.com/help/mcve

populating a column with an existing cell info based on a adjacent populated column

1 Answers