Selecting Data in Column

[Rex Dunlap]

The following code gets the last data cell in column B:
Range("b20000").End(xlUp).Select

I would now like to select the Range("Activecell:B2"). How
do I do that?

Thank you for your help.

 

[Frank Kabel]

Hi
try
range(cells(2,2),cells(ActiveSheet.Cells(Rows.Count,
"B").End(xlUp).row,2)).select

though in most cases it is not required to select cells within in
macro

 

[Bob Phillips]

Hi Rex,

Try

Range("B2:B" & RANGE("B20000").End(xlUp).Row).Select


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Rex]

Frank, just so I learn this correctly, you say that I
don't need to select cells to do something with a macro.

What I wanted to was select all the blank cells in B and
put the word "NO" in them. With your help this is what I
have:

Range("b20000").End(xlUp).Select
Range(Cells(5, 2), Cells(ActiveSheet.Cells
(Rows.Count, "B").End(xlUp).Row, 2)).Select
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.Value = "NO"
Range("A4").Select

Would you do it differently?

Thanks for your help in at least getting me this far and
saving me so much time!

Kind regards,
Rex

 

[Bob Phillips]

This

Range(Cells(5, 2), Cells(ActiveSheet.Cells(Rows.Count, "B").End(xlUp).Row,
2)).Select
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.Value = "NO"
Range("A4").Select

can be written as

Range("B5:B" & Range("B" &
Rows.Count).End(xlUp).Row).SpecialCells(xlCellTypeBlanks).Value = "NO"

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Rex]

Thank you very much, Bob.

-----Original Message-----
This

Range(Cells(5, 2), Cells(ActiveSheet.Cells

(Rows.Count, "B").End(xlUp).Row,

2)).Select
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.Value = "NO"
Range("A4").Select

can be written as

Range("B5:B" & Range("B" &
Rows.Count).End(xlUp).Row).SpecialCells

(xlCellTypeBlanks).Value = "NO"