Selecting Many Non-Adjacent Rows Easily

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

In a spreadheet with 100's of rows, how do I easily or automatically select non-adjacent rows at a constant interval, eg every 5 rows of an 800 row sheet?
 
Hi,

Assign this macro to a keyboard shortcut.

Sub jumpLines()
jumpLine = 5
Selection.Offset(jumpLine, 0).Select
End Sub

jeff
-----Original Message-----
In a spreadheet with 100's of rows, how do I easily or
Click to expand...
automatically select non-adjacent rows at a constant
interval, eg every 5 rows of an 800 row sheet?
 
What are you looking to achieve? It may be that there are far easier ways of
doing what you want.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------



Larry Marvet said:
In a spreadheet with 100's of rows, how do I easily or automatically select
Click to expand...
non-adjacent rows at a constant interval, eg every 5 rows of an 800 row sheet?
 
Larry

This code will delete every 5th row starting with row 2.

Sub Delete_Alt_Rows()
''originally posted by Ron de Bruin
Application.ScreenUpdating = False
Range("Z1:Z1000").Value = 1
Dim a As Integer
For a = 2 To 1000 Step 5
Cells(a, "Z").Resize(4, 1).Value = ""
Next
Columns("Z").Cells.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
Application.ScreenUpdating = True
End Sub

Gord Dibben Excel MVP
 
A non-macro approach.

Insert a helper column.
=mod(row(),5)
and drag down

Apply data|filter|autofilter to that column
Filter by the value you want (3?)

Then select the visible rows
then Edit|goto (or F5 or ctrl-g)
click special
select visible cells only

Data|filter|showall

Do whatever you wanted to do.

Delete that helper column.
 
More information on the problem

Specifically, I get too much data from an online machine: spectrographic information every nanometer of wavelength, but my analysis spreadsheet can only handle data at 5 nm wavelength. So I have a spreadsheet where I need to delete 4 of every 5 rows before I can paste it into the analysis spreadsheet. But I don't want to do this by hand

Larry
 
No problem, both Gord and Dave have given you an option of a VBA or non-VBA
approach. :-)

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------



Larry said:
More information on the problem:

Specifically, I get too much data from an online machine: spectrographic
Click to expand...
information every nanometer of wavelength, but my analysis spreadsheet can only
handle data at 5 nm wavelength. So I have a spreadsheet where I need to delete 4
of every 5 rows before I can paste it into the analysis spreadsheet. But I don't
want to do this by hand.
 
Back
Top