Macro Excel put values down columns

[jayserna]

Hello I have this macro and it works fine, except... The only issue I am having is my data is not consistent. In column "O" some of the rows have the client name and some don't. I need to update my macro so that when it runs all rows in column "O" are completed with the client name. The value is the same for every row in Column "O" except for the header.

Is there a way I can pull the value from "O" and assign it to all rows in the column?

Also there is a macro to identify which is the last row in the sheet, please help.

Sub Awesome()

Dim Ret
Dim Lastrow As Long

Set AC = ActiveWorkbook.ActiveSheet

Ret = Application.GetOpenFilename("Text Files (*.txt), *.txt")
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row


If Ret <> False Then
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;" & Ret, Destination:=Range("$A$2"))
.Name = "*.txt"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileOtherDelimiter = "|"
.TextFileColumnDataTypes = Array(2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, _
2, 2, 2, 2, 2, 2)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
Cells.Select
Selection.ColumnWidth = 9.14

End With
End If


Call Merged 'Merged Macro
Call Sort 'Sorting Macro


Worksheets("AC").Range("O2:O" & Lastrow).Value = ??????? 'Adds Client Name to all rows

End Sub

 

[chicagocomputerclasses]

Assuming that your first column always has values, you should be able to use this

Worksheets("AC").Range("O2:O" & cells(rows.count,1).end(xlup).row).Value = range("O" & range("O1").end(xldown).row).value

 

[SpillerBD]

I do not understand where the "customer name" values do occur.
Is there only one Customer in this data import? or many?
Does the name occur like a section/group header? Only once and then not repeated?

 

[JLGWhiz]

Sub fillO()
Dim dat As String, lr As Long
With ActiveSheet 'Substitute your sheet here, eg. Sheets("Data")
    lr = .Cells.Find("*", , xlValues, xlPart, xlByRows, xlPrevious).Row
    dat = .Cells(Rows.Count, "O").End(xlUp).Value
    .Range("O2") = dat
    .Range("O2:O" & lr).FillDown
End With
End Sub

 

[jayserna]

I do not understand where the "customer name" values do occur.
Is there only one Customer in this data import? or many?
Does the name occur like a section/group header? Only once and then not repeated?

Customer name is the same for entire sheet.
Problem is the customers send incomplete data and column O is sometimes left empty, but it needs to be filled.
All data is column O is the same for every row

 

[jayserna]

Sub fillO()
Dim dat As String, lr As Long
With ActiveSheet 'Substitute your sheet here, eg. Sheets("Data")
    lr = .Cells.Find("*", , xlValues, xlPart, xlByRows, xlPrevious).Row
    dat = .Cells(Rows.Count, "O").End(xlUp).Value
    .Range("O2") = dat
    .Range("O2:O" & lr).FillDown
End With
End Sub

This worked!

 

[JLGWhiz]

This worked!

Thanks for the feedback,
regards, JLG