format last 20 rows
 

I've got a very long spreadsheet that I copy information to every day. I
would like to format the text that is copied over via a range command to be
formatted like the existing text in the document which would mean I need to
change the following charactristics
.HorizontalAlignment = xlLeft
Selection.Font.ColorIndex = 0
Selection.Font.Bold = False

Is there a way, since this speadsheet has over 50,000 rows to just designate
the last 30 cells in col B have the above format?

Thanks

format last 20 rows
 

MyLastRow = Cells(1, 1).CurrentRegion.Rows.Count
With Rows(CStr(MyLastRow - 19) & ":" & CStr(MyLastRow))
.HorizontalAlignment = xlLeft
.Font.ColorIndex = 0
.Font.Bold = False
End With

Vlado

"JOUIOUI" wrote:

I've got a very long spreadsheet that I copy information to every day. I
would like to format the text that is copied over via a range command to be
formatted like the existing text in the document which would mean I need to
change the following charactristics
.HorizontalAlignment = xlLeft
Selection.Font.ColorIndex = 0
Selection.Font.Bold = False

Is there a way, since this speadsheet has over 50,000 rows to just designate
the last 30 cells in col B have the above format?

Thanks

format last 20 rows
 

here's one way

Sub test()
Dim lastrow As Long
lastrow = Cells(Rows.Count, "B").End(xlUp).Row - 19

With Range("b" & lastrow).Resize(20, 1)
.HorizontalAlignment = xlLeft
.Font.ColorIndex = 0
.Font.Bold = False
End With
End Sub

--


Gary


"JOUIOUI" wrote in message
...
I've got a very long spreadsheet that I copy information to every day. I
would like to format the text that is copied over via a range command to be
formatted like the existing text in the document which would mean I need to
change the following charactristics
.HorizontalAlignment = xlLeft
Selection.Font.ColorIndex = 0
Selection.Font.Bold = False

Is there a way, since this speadsheet has over 50,000 rows to just designate
the last 30 cells in col B have the above format?

Thanks