Tuesday, January 10, 2006

Multiple Row Selection in Excel VBA

I have a real short article today to address a couple of Excel questions that have come my way. I am researching a good topic, and I didn’t want to go without posting anything in the mean time. Below is a simple VBA Excel macro that will do three things. First it will prompt the user to select the whole active sheet, then prompt the user to select multiple rows, then it will select the C10 cell, and activate the row for that ActiveCell, and finally will select a single cell and remove all rows preceding it, starting from row 1.

Sub Selections()
    'prompt user to let them know you will select the whole sheet
    MsgBox "Ready to select whole sheet?", vbOKOnly
    
    'Select whole sheet
    Cells.Select
        
    'prompt user to let them know you are going to select multiple rows
    MsgBox "Ready to select rows 2 through 5?", vbOKOnly
    
    'Select multiple rows
    Range("2:2", "5:5").Select
    
    'prompt user to let them know you are going to select multiple rows and delete
    MsgBox "Select row of current activecell", vbOKOnly
    
    'Select some cell
    Range("C10").Select
              
    'Select row of current active cell
    Range(ActiveCell.Row & ":" & ActiveCell.Row).Select
              
    'prompt user to let them know you are going to select multiple rows and delete
    MsgBox "Delete rows 1 up to active cell?", vbOKOnly
              
    'Select some cell
    Range("C10").Select
              
    'Delete all rows from row 1 up to the currently selected row
    Range("1:1", ActiveCell.Row & ":" & ActiveCell.Row).Delete
End Sub

I also would like to thank Bonnie Taylor for the help she has provided me over the past several months with getting started on this blog. Her patience with my incredibly bad grammar grants her nothing less than sainthood in my eyes, and the educational aspect went both ways. Her help was greatly appreciated, and she will be missed. I wish her well on her future endeavors.

3 comments:

Anonymous said...

Hi, I have a question about deleting all rows up to the current row.

I'm trying to write a script that will delete all rows below the current row up to row 65000. I tried to use your script and it gave me the following error:

"Run-time error '1004':
Method 'Range' of object '_Global' failed"

Here is the modified script:

Range("A2").Select
Do While ActiveCell.Offset(0, 1) <> ""
ActiveCell.Offset(1, 0).Select
Loop
Range(ActiveCell.Row & " : " & ActiveCell.Row, "65000:65000").Delete


Any help would be greatly appreciated.

Anonymous said...

try ":" rather than " : "

Anonymous said...

Your "Select row of current active cell" just saved my week.
Thank you.