VBA formatting to last non-blank cell

[lzopatti]

I'm trying to write a macro to format a header row, but don't know how to do so until the last non-blank cell (files may have different numbers of columns). Here's what I have, clearly I don't need to go up to column XFD. how do I stop at a non-blank cell? thanks!!!

Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Range("B7:XFD7").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1
.TintAndShade = -0.249977111117893
.PatternTintAndShade = 0

 

[schielrn]

Welcome to the Board!

Try:

Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
With range("B7",cells(7,columns.count).end(xltoleft)).Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1
.TintAndShade = -0.249977111117893
.PatternTintAndShade = 0

Hope that helps.

 

[lzopatti]

thanks - that was quick!

another thing - I'm trying to fill a formula down to the last non-blank row... this is what I have so far (and it's ugly!):

Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Range("E125").Select
Selection.Copy
Range("D125").Select
Selection.End(xlDown).Select
Selection.End(xlDown).Select
Selection.End(xlDown).Select
Selection.End(xlDown).Select
Range("E1048576").Select
Range(Selection, Selection.End(xlUp)).Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
ActiveWindow.ScrollRow = 1048548
ActiveWindow.ScrollRow = 1044444
ActiveWindow.ScrollRow = 1021873
ActiveWindow.ScrollRow = 976730
ActiveWindow.ScrollRow = 923379
ActiveWindow.ScrollRow = 870028
ActiveWindow.ScrollRow = 800262
ActiveWindow.ScrollRow = 740755
ActiveWindow.ScrollRow = 695612
ActiveWindow.ScrollRow = 654573
ActiveWindow.ScrollRow = 611482
ActiveWindow.ScrollRow = 545820
ActiveWindow.ScrollRow = 498625
ActiveWindow.ScrollRow = 447326
ActiveWindow.ScrollRow = 389872
ActiveWindow.ScrollRow = 342677
ActiveWindow.ScrollRow = 293430
ActiveWindow.ScrollRow = 248287
ActiveWindow.ScrollRow = 199040
ActiveWindow.ScrollRow = 176469
ActiveWindow.ScrollRow = 141586
ActiveWindow.ScrollRow = 131326
ActiveWindow.ScrollRow = 121066
ActiveWindow.ScrollRow = 100547
ActiveWindow.ScrollRow = 86183
ActiveWindow.ScrollRow = 67715
ActiveWindow.ScrollRow = 59508
ActiveWindow.ScrollRow = 47196
ActiveWindow.ScrollRow = 41040
ActiveWindow.ScrollRow = 30780
ActiveWindow.ScrollRow = 22572
ActiveWindow.ScrollRow = 20521
ActiveWindow.ScrollRow = 18469
ActiveWindow.ScrollRow = 16417
ActiveWindow.ScrollRow = 12313
ActiveWindow.ScrollRow = 10261
ActiveWindow.ScrollRow = 8209
ActiveWindow.ScrollRow = 6157
ActiveWindow.ScrollRow = 4105
ActiveWindow.ScrollRow = 2053
ActiveWindow.ScrollRow = 1
ActiveWindow.ScrollRow = 2053
ActiveWindow.ScrollRow = 4105
ActiveWindow.ScrollRow = 6157
ActiveWindow.ScrollRow = 4105
ActiveWindow.ScrollRow = 2053
ActiveWindow.ScrollRow = 1
Range("D17").Select
ActiveWindow.LargeScroll Down:=1
Range("D45").Select
ActiveWindow.LargeScroll Down:=1
Range("D74").Select
ActiveWindow.LargeScroll Down:=1
Range("D103").Select
ActiveWindow.LargeScroll Down:=1
Range("D132").Select
ActiveWindow.LargeScroll Down:=1
Range("D161").Select
ActiveWindow.LargeScroll Down:=1
Range("D190").Select
ActiveWindow.LargeScroll Down:=1
Range("D219").Select
ActiveWindow.LargeScroll Down:=-1
Range("D190").Select
ActiveWindow.LargeScroll Down:=-1
Range("D161").Select
ActiveWindow.LargeScroll Down:=-1
Range("D132").Select
ActiveWindow.LargeScroll Down:=-1
Range("D103").Select
ActiveWindow.LargeScroll Down:=-1
Range("D74").Select
ActiveWindow.LargeScroll Down:=-1
Range("D45").Select
ActiveWindow.LargeScroll Down:=-1
Range("H28").Select
Application.CutCopyMode = False
Selection.Copy
Range("G28").Select
Selection.End(xlDown).Select
Selection.End(xlDown).Select
Selection.End(xlDown).Select
Selection.End(xlDown).Select
Range("H1048576").Select
Range(Selection, Selection.End(xlUp)).Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

 

[lenze]

Hi and welcome to the board!!!
There may be a better way for your 2nd question!!
What is the formula you want and where do you want it??
lenze

 

[lzopatti]

ADVERTISEMENT

Thanks! The formula is:

=CONCATENATE(C8," ",D8)

And I want to fill it down column E, to the last non-blank in column D.

 

[lenze]

Assuming you want to start in Row 8 or Column "E"

Sub AddFormula()
Dim LR As Long
LR = Cells(Rows.Count, "D").End(xlUp).Row
With Range("$E$8:$E" & LR) 'Change this line if needed
      .Formula = "=CONCATENATE(C8,"" "",D8)"
End With
End Sub

BTW: You can add a line to change the formula to Values

Sub AddFormula()
Dim LR As Long
LR = Cells(Rows.Count, "D").End(xlUp).Row
With Range("$E$8:$E" & LR) 'Change this line if needed
     .Formula = "=CONCATENATE(C8,"" "",D8)"
     .Value = .Value
End With
End Sub

 

[lzopatti]

thanks!!!