Halfway to End

[Rokuro kubi]

How do I select the bottom half of a column's cells in a macro?

 

[Don Guillett]

with/without blanks and why

 

[Bob Umlas]

Sub Untested()
Rw = Range("A65536").End(xlUp).Row 'change "A" to the column you're
interested in
Rw = Rw \ 2
Range(Range("A1").Offset(Rw), Range("A65536").End(xlUp)).Select
End Sub

 

[bplumhoff]

Hello,

For example:
Dim i As Long, j As Long
i = Range("A1").Column
j = Cells(65536, i).End(xlUp).Row
Range(Cells(j / 2 + 1, i), Cells(j, i)).Select

HTH,
Bernd

 

[Don Guillett]

Sub selecthalfcolumn()'ignores blanks
lr = Cells(Rows.Count, "a").End(xlUp).Row / 2
Range(Cells(1, 1), Cells(lr, 1)).Select
End Sub

 

[Gary''s Student]

Because a column always contains 65536 cells, the bottom half is 32769 thru
65536. For example:

Sub gsnu()
Range("A32769:A65536").Select
End Sub

 

[Rokuro kubi]

with/without blanks and why

Hi Don,
The sheet is fairly uniform - there will be data in every cell in that
column until the last usedrow of the sheet. Exactly half of the
figures in column F will be duplicates and column E will be always be
one of two values. I need to lose the data directly adjacent to all
cells showing Value 2.

 

[Rokuro kubi]

Sub selecthalfcolumn()'ignores blanks
lr = Cells(Rows.Count, "a").End(xlUp).Row / 2
Range(Cells(1, 1), Cells(lr, 1)).Select
End Sub

That's great Don, cheers

 

[Don Guillett]

65536 wont be true with next xl version