VBA Select from top to end of column, part 2

[opislak]

Hi all,

I try to select a variable range of cells in a column, in order to set a standard lay-out (background, borders, etc.).
This code does the job allright:
VBA Code:
Range("A3:D999,E3:P999,Q3:Q999,R3:T999,U3:U999,V3:V999,W3:AH999").Select
However, if the data in the column doesn't reach up to row 999, why select that deep? Is there a possibility to select only rows with data in them. Something like this:
VBA Code:
Range("D3", Selection.End(xlDown)).Style = "Percent"
This works fine, too. Can both lines be somehow combined?

Thanks for responses!!
Patrick.

 

[Lian]

Dim iLastCell as long
iLastCell = Cells(rows.count,"D").end(xlup).row
Range("D3:D" & iLastCell).Style = "Percent"

 

[footoo]

Dim lr&: lr = [A:AH].Find(What:="*", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Intersect([A:AH], Rows("3:" & lr)).Style = "Percent"

 

[opislak]

Many thanks for your efforts, much appreciated.
But... Sorry, not what I'm looking for...
(I copied the "percent"-thing for information from another part of my script to show as a working example).
However, I would like to start another sub - end sub after the selection.
So: select an interrupted range, but instead of the row 999, I would like to make this last row dynamic, according to the length of the longest column (not all columns are edited).

sub SelectColumns()
    Range("A3:D999,E3:P999,Q3:Q999,R3:T999,U3:U999,V3:V999,W3:AH999").select
    DoSomething 'sub that does something else, e.g. paint background or put borders of a certain color, etc.
end sub

 

[Fluff]

How about like

Sub opislak()
   Dim UsdRws As Long
   
   UsdRws = Range("A:AH").Find("*", , , , xlByRows, xlPrevious, , , False).Row
   With Intersect(Rows("3:" & UsdRws), Range("A:AH"))
      .Interior.Color = 45678
      .Borders.Weight = xlThin
   End With
End Sub

 

[opislak]

How about like

Sub opislak()
   Dim UsdRws As Long
  
   UsdRws = Range("A:AH").Find("*", , , , xlByRows, xlPrevious, , , False).Row
   With Intersect(Rows("3:" & UsdRws), Range("A:AH"))
      .Interior.Color = 45678
      .Borders.Weight = xlThin
   End With
End Sub

Dear Fluff,
Almost forgot to reply, for which my apologies.
Your code works fine, thank you.
Problem resolved. Many thanks.

 

[Fluff]

You're welcome & thanks for the feedback.