Select range on a worksheet
 

Hi all.

I do a lot of downloads with CSV files that I pull into Excel and then
have to format, so I wrote a macro that does 99% of the formatting I
need to do, but I'm having the darndest time getting a macro to select
the whole range that has values in it starting at Cell A1 and going to
the last cell... which might be F500, Q1250, or whatever, depending on
the worksheet. All I want to do is select the whole range and then
put borders on the cells.

Any help?

Select range on a worksheet
 

Give this a try...

Sub SelectRange() 'Run me
Range("A1", LastCell()).Select
End Sub

Public Function LastCell(Optional ByVal wks As Worksheet) As Range
Dim lngLastRow As Long
Dim intLastColumn As Integer

If wks Is Nothing Then Set wks = ActiveSheet
On Error Resume Next
lngLastRow = wks.Cells.Find(What:="*", _
After:=wks.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
intLastColumn = wks.Cells.Find(What:="*", _
After:=wks.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Column
On Error GoTo 0
If lngLastRow = 0 Then
lngLastRow = 1
intLastColumn = 1
End If
Set LastCell = wks.Cells(lngLastRow, intLastColumn)

End Function
--
HTH...

Jim Thomlinson


"Jim" wrote:

Hi all.

I do a lot of downloads with CSV files that I pull into Excel and then
have to format, so I wrote a macro that does 99% of the formatting I
need to do, but I'm having the darndest time getting a macro to select
the whole range that has values in it starting at Cell A1 and going to
the last cell... which might be F500, Q1250, or whatever, depending on
the worksheet. All I want to do is select the whole range and then
put borders on the cells.

Any help?