Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old March 23rd 07, 09:49 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 98
Default Select all rows except header row in one column

Hi,

I've got the following code to select all data apart from the header row...

with activesheet
.rows("2:" & .rows.count).select
end with

How do I select all data in a certain column apart from the header row
without selecting right doen to the bottom of the sheet?

Thanks, Mel

  #2   Report Post  
Old March 23rd 07, 10:08 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2006
Posts: 226
Default Select all rows except header row in one column

Hi Mel

you could get the last row using .end something like

Dim i As Integer
i = [a1].End(xlDown).Row

will pass the number of the last row to the integer "i" then use in
your code

with activesheet
.rows("2:" & i).select
end with

hope this is of some help to you

S

  #3   Report Post  
Old March 23rd 07, 10:12 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Sep 2006
Posts: 340
Default Select all rows except header row in one column

In practice you can usually just select the columns and press ctrl
+shift+\.

But if you want code try:

Application.Goto Reference:="R2C:R65536C"
Range(Selection, Selection.End(xlUp)).Select


On 23 Mar, 09:49, Meltad wrote:
Hi,

I've got the following code to select all data apart from the header row...

with activesheet
.rows("2:" & .rows.count).select
end with

How do I select all data in a certain column apart from the header row
without selecting right doen to the bottom of the sheet?

Thanks, Mel



  #4   Report Post  
Old March 23rd 07, 10:31 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 98
Default Select all rows except header row in one column

Hi Incidental,

Thanks for your reply, I used your code but it worked across all columns,
how do i specify for column N only?

Thanks, Mel



"Incidental" wrote:

Hi Mel

you could get the last row using .end something like

Dim i As Integer
i = [a1].End(xlDown).Row

will pass the number of the last row to the integer "i" then use in
your code

with activesheet
.rows("2:" & i).select
end with

hope this is of some help to you

S


  #5   Report Post  
Old March 23rd 07, 12:14 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2006
Posts: 226
Default Select all rows except header row in one column

Hi

Sorry i must not have read your post correctly the first time through.

rather than selecting the rows you can set a range which is pretty
much the same similar to what you already have.

if there are blanks in your column you will have to find the .end ref
from the bottom up i.e. i=[N65536].end (xlup).row

if not though this code will work for you.

Dim i As Integer
i = [N1].End(xlDown).Row

with activesheet
.Range("N2", "N" & i).Select
end with


S



  #6   Report Post  
Old March 23rd 07, 04:05 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 3,986
Default Select all rows except header row in one column

Meltad, if you want to select column "A" except for header then:

Sub GetRng()
Dim myRange As Variant
lastRow = Cells(Rows.Count, 1).End(xlUp).Row 'Gives row number for last cell
in "A"
Set myRange = Range("A2:A" & lastRow)
myRange.Select
End Sub

"Lori" wrote:

In practice you can usually just select the columns and press ctrl
+shift+\.

But if you want code try:

Application.Goto Reference:="R2C:R65536C"
Range(Selection, Selection.End(xlUp)).Select


On 23 Mar, 09:49, Meltad wrote:
Hi,

I've got the following code to select all data apart from the header row...

with activesheet
.rows("2:" & .rows.count).select
end with

How do I select all data in a certain column apart from the header row
without selecting right doen to the bottom of the sheet?

Thanks, Mel






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
Need to select rows to average based on a value in a different column Randy K New Users to Excel 1 February 15th 06 01:03 AM
Select max value in a row and pick the column header EK Excel Discussion (Misc queries) 8 July 21st 05 02:10 PM
Select multiple rows and add a value in a column AJM1949 Excel Programming 2 June 7th 05 11:16 PM
how would i select a group of rows based on a column value Brian Walker Excel Programming 1 June 15th 04 02:31 AM
Select rows contain Xin Column Y Hafeez Esmail Excel Programming 3 November 11th 03 08:11 PM


All times are GMT +1. The time now is 11:19 AM.

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

About Us

"It's about Microsoft Excel"

 

Copyright © 2017