vba - Select Used Range minus the top header
 

Hi all,

I am using "ActiveSheet.UsedRange.Select" to select the used range and
that's great. However, I can't figure out how to ignore the first row
of data, because it's a header. I'm still searching the boards but I
haven't seen anyone ask this question.

Thanks
John

Answer: vba - Select Used Range minus the top header
 

Hi John,

To select the used range minus the top header, you can use the following VBA code:

This code first finds the last row of data in column A using the End method. Then it sets a range object rng to start from cell A2 and extend to the last row of data minus one (to exclude the header row), and to cover all columns in the used range. Finally, it selects the rng range.

1. Declare variables lastRow and rng as Range.
2. Find the last row of data in column A using the End method.
3. Set the rng range object to start from cell A2 and extend to the last row of data minus one (to exclude the header row), and to cover all columns in the used range.
4. Select the rng range.

vba - Select Used Range minus the top header
 

One way?

Sub SelectUsedRangeLessTopRow()
With ActiveSheet.UsedRange
mr = .Rows.Count
mc = .Columns.Count
.Range(Cells(2, 1), Cells(mar, mc)).Select
End With
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Johnny" wrote in message
...
Hi all,

I am using "ActiveSheet.UsedRange.Select" to select the used range and
that's great. However, I can't figure out how to ignore the first row
of data, because it's a header. I'm still searching the boards but I
haven't seen anyone ask this question.

Thanks
John

vba - Select Used Range minus the top header
 

Sub SelectUsedRangeLessTopRow()
With ActiveSheet.UsedRange
mr = .Rows.Count
mc = .Columns.Count
* * * * .Range(Cells(2, 1), Cells(mar, mc)).Select
End With
End Sub


Don,

Thank you very much for your reply. I'm only fixing/posting the typos
in case anyone else wants to use this:


Sub SelectUsedRangeLessTopRow()
With ActiveSheet.UsedRange
mr = .Rows.Count
mc = .Columns.Count
Range(Cells(2, 1), Cells(mar, mc)).Select
End With

End Sub

vba - Select Used Range minus the top header
 

A couple mo

With ActiveSheet.UsedRange
.Offset(1, 0).Resize(.Rows.Count - 1).Select
End With

Or if you always wanted to select A2 through the lastusedcell:

With ActiveSheet
.Range("a2", .Cells.SpecialCells(xlCellTypeLastCell)).Select
End With

There's a difference between these two. The .usedrange doesn't have to start in
A1.



Johnny wrote:

Hi all,

I am using "ActiveSheet.UsedRange.Select" to select the used range and
that's great. However, I can't figure out how to ignore the first row
of data, because it's a header. I'm still searching the boards but I
haven't seen anyone ask this question.

Thanks
John

--

Dave Peterson

vba - Select Used Range minus the top header
 

Try

With Worksheets(1).UsedRange
.Cells(2, 1).Resize(.Rows.Count - 1, .Columns.Count).Select
End With


--
Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)



"Johnny" wrote in message
...
Hi all,

I am using "ActiveSheet.UsedRange.Select" to select the used range and
that's great. However, I can't figure out how to ignore the first row
of data, because it's a header. I'm still searching the boards but I
haven't seen anyone ask this question.

Thanks
John