HELP: Row Selection and VBA guides

Hi all,

just two quick questions, requiring two quick and simple answers.

I have searched the help file and tips and cannot get the answers I need:

1) what code will allow me to select the row within which the active cell is.

Eg. I have created a loop to find a certain text within a spreadsheet and once I have found that text then the cell that the text is in becomes the active cell. What code will select the row that the text is in?

2) What are the best text books available for VBA programming. I am just getting into it and would like a thorough and clear manual.

Please let me know if more infor on problem 1 is needed.

Many thanks,

Pedros.

1: Rows(ActiveCell.Row).select
2: Excel 2003 Power Programming with VBA

Thanks Kaak

Perfect.

Kaak, or anyone else that may be able to help......

further to my question above, is there a way to select the row containing the active cell and all rows below that until there is a value in the same column as the active cell?

for example, the code below colours all cells below the active cell yellow until the next cell in the column contains a value. I want a similar action but with row select.

Do While ActiveCell = ""
Selection.Interior.ColorIndex = 6
ActiveCell.Offset(1, 0).Activate
Loop

Note the active cell is in column B.

FirstRow = ActiveCell.Row
Do While ActiveCell = ""
Selection.Interior.ColorIndex = 6
ActiveCell.Offset(1, 0).Activate
Loop
LastRow = ActiveCell.Row
Rows(FirstRow & ":" & LastRow).select

Thanks, Kaak.

I am getting very close to what I require now.

The only problem is that this selects the rows below until there is a value in a cell in column A.

I need the Rows to be selected below until there is a value in column B.

I could get around this by writing in a function to delete the first column but I would like to do it based on column B if possible.

Is this achievable?

P.s. I have purchase the text you recommended so hopefully with some studying I will not need to post such simple requests on here

Kaak,

Thanks for your help. with you code as a basis I was able to figure out a way of completing the task I wanted.

I used:



Rows(ActiveCell.Row).Select
ActiveCell.Offset(0, 1).Activate

FirstRow = ActiveCell.Row
ActiveCell.Offset(1, 0).Activate
Do While ActiveCell = ""
ActiveCell.Offset(1, 0).Activate
Loop
ActiveCell.Offset(-1, 0).Activate
LastRow = ActiveCell.Row
Rows(FirstRow & ":" & LastRow).Select


End Sub

If my coding is clumsy feel free to tidy it up but it seems to work.

thanks again, I really appreciate your help!!!!!

You can select a Beginners cell in Column B

The problem is that I don't know your aim for the macro.

p.s. The book is a very good book you will not regret buying it