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

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

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

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

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

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