highlight group of rows

L

lilleke

work with Excel 2000 - direct mail databases - I would like to be able to
start on any row I choose and have a macro to choose the very next 2500 rows.
Is this possible?
 
I

Ian

If all you want to do is select 2500 rows buy clicking on a cell, then the
following will do.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Range("A" & ActiveCell.Row, "A" & ActiveCell.Row + 2499).EntireRow.Select
End Sub

This will select the current row and the following 2499 (2500 in total).

It's not a lot of use as it stands and clicking elsewhere in the sheet will
automatically change the selection. It's not clear from your post exactly
what you need to do, except to subject lots of unfortunates to junk mail :)
 
D

Don Guillett

or
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
mr = Target.Row
Range(Cells(mr, 1), Cells(mr + 2499, Columns.Count)).Select
End Sub
 
L

lilleke

sorry, I am not good at creating macros - i need step by step please.
I copied/pasted the Private Sub into new macro in personal.xls and when I
went to run it, it was not found.

Please type it so I can copy/paste into excel2000
I did alt+F11
insert Module
then what?

Yes, all I want to do is select the next 2500 rows in direct mail database
so I can copy to new worksheet and process as a mailing.
 
I

Ian

On the sheet you want to copy from:
Right click on the tab name and select View Code
In the new Visual Basic window:
At the top of the new window, where it says General, click the down arrow
next to it and select Worksheet
In the large window you will see the first and last lines of the code.
Copy the other lines of code between these supplied then close the Visual
Basic window.
Back on your sheet:
The code will run automatically and every time you click on another cell,
that row and the 2499 rows below will be selected.
Click the first row you want copied
Go to Edit>Copy (or Ctrl-C)
In your destination sheet
Select the row where you want the data to start (column A, I assume)
Go to Edit>Paste (or Ctrl-V)
 
D

Don Guillett

This code goes in the SHEET module by right click on the sheet
tab>copy/paste as written> Save the workbook. Now when you select a cell,
that row and 2499 more will be (selected) highlighted.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
[email protected]
lilleke said:
sorry, I am not good at creating macros - i need step by step please.
I copied/pasted the Private Sub into new macro in personal.xls and when I
went to run it, it was not found.

Please type it so I can copy/paste into excel2000
I did alt+F11
insert Module
then what?

Yes, all I want to do is select the next 2500 rows in direct mail database
so I can copy to new worksheet and process as a mailing.
Click to expand...
 
L

lilleke

ok, it works beautifully, however, this is a database where I find names and
delete names, thus clicking on cells.

Can I have maco on demand. I mean it won't run when I click on a cell - only
when I call it up with a button

I also need this in the personal.xls for all my dbases (about 14) how to get
it there.
--
claudia


Ian said:
On the sheet you want to copy from:
Right click on the tab name and select View Code
In the new Visual Basic window:
At the top of the new window, where it says General, click the down arrow
next to it and select Worksheet
In the large window you will see the first and last lines of the code.
Copy the other lines of code between these supplied then close the Visual
Basic window.
Back on your sheet:
The code will run automatically and every time you click on another cell,
that row and the 2499 rows below will be selected.
Click the first row you want copied
Go to Edit>Copy (or Ctrl-C)
In your destination sheet
Select the row where you want the data to start (column A, I assume)
Go to Edit>Paste (or Ctrl-V)
Click to expand...
 
D

Don Guillett

Just change this
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
mr = Target.Row
Range(Cells(mr, 1), Cells(mr + 2499, Columns.Count)).Select
End Sub

to this>place in a module in your Personal.xls and assign a button (or
custom menu button) to it.

Sub selectrows()
mr = activecell.row
Range(Cells(mr, 1), Cells(mr + 2499, Columns.Count)).Select
End Sub

However, I think you could have a better macro to do it all if you would
only FULLY explain your needs the first time.
 
L

lilleke

Sorry if I was unclear originally.

created module in Personal.xls w/SUB Selectrows()
went to database, highlighted first row, ran macro, got "runtime error 424,
Object Required" - what does this mean?

Here is the deal: I work w/large databases, about 14 of them, whereby I
choose a mailing list of 2500 names per week to mail flyers to. Currently I
highlight rows with my mouse. Then copy highlighted info to new worksheet to
process. Hope that helps.

Ideally I would like to highlight first row I want, run macro that would
highlight the next 2499 rows copy and paste into new worksheet. I think that
is asking too much. If I could just highlight rows, that would help
tremendously. Thank you.
 
Top