Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 6
Default 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
  #2   Report Post  
Excel Super Guru
 
Posts: 1,867
Thumbs up 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:

Formula:
[b]Dim lastRow As Long
Dim rng 
As Range[/b]

[
b]lastRow[/b] = ActiveSheet.Cells(Rows.Count1).End(xlUp).Row
[b]Set rng[/b] = Range("A2").Resize(lastRow 1ActiveSheet.UsedRange.Columns.Count)

[
b]rng.Select[/b
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.
__________________
I am not human. I am an Excel Wizard
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 10,124
Default 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


  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 6
Default 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
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 35,218
Default 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


  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 7,247
Default 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


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to select Visible Cells range minus 2 rows (frozen panes) LuisE Excel Programming 5 November 22nd 07 05:01 AM
How can I select a range of cells for header? emil Excel Programming 2 June 11th 06 10:38 AM
CHANGE TRAILING MINUS TO BRACKETS OR PRECEEDING MINUS Socal Analyst looking for help Excel Discussion (Misc queries) 2 May 12th 06 07:17 PM
Find and select LastRow Minus 1 Carlie[_5_] Excel Programming 4 April 17th 06 10:35 PM
Range Minus operator? Zbigniew Malinowski Excel Programming 6 November 21st 04 07:27 PM


All times are GMT +1. The time now is 01:17 AM.

Powered by vBulletin® Copyright ©2000 - 2023, Jelsoft Enterprises Ltd.
Copyright 2004-2023 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"