Excel worksheet function

  • Thread starter Thread starter witteman
  • Start date Start date
W

witteman

Anyone knows of a simple way with Excel how to 'skip' rows of an array
at a regular interval say every two steps (delete or hide every other
row). I have a lot of data (two colums) and only need half the data so
I manually delete every second row. I tried to do this with VLOOKUP and
Filter but no luck.

Thanks

Fran
 
witteman said:
Anyone knows of a simple way with Excel how to 'skip' rows of an array
at a regular interval say every two steps (delete or hide every other
row). I have a lot of data (two colums) and only need half the data so
I manually delete every second row. I tried to do this with VLOOKUP and
Filter but no luck.

Thanks

Frank
Click to expand...

Maybe not the easiest, but you wont lose the data.
Grab and move the column edge to minimum for those columns.
OR
Using the control key down, select all undesirable columns, then go to
format, columns, hide.
 
vernon said:
"witteman" (e-mail address removed) wrote in message

Anyone knows of a simple way with Excel how to 'skip' rows of an
array
at a regular interval say every two steps (delete or hide every other
row). I have a lot of data (two colums) and only need half the data
so
I manually delete every second row. I tried to do this with VLOOKUP
and
Filter but no luck.

Thanks

Frank


--
witteman

Maybe not the easiest, but you wont lose the data.
Grab and move the column edge to minimum for those columns.
OR
Using the control key down, select all undesirable columns, then go to
format, columns, hide.
Click to expand...

Hi Vernon,

I want to delete rows (every second row) not columns and I don't mind
loosing data as I will save the edited file under a new name.

Thanks for your reply

Frank
 
witteman said:
Anyone knows of a simple way with Excel how to 'skip' rows of an array
at a regular interval say every two steps (delete or hide every other
row). I have a lot of data (two colums) and only need half the data so
I manually delete every second row. I tried to do this with VLOOKUP and
Filter but no luck.

Thanks

Frank
Click to expand...

I got help from the Excel newsgroup after all in the form of a simple
macro that hides every (nth) row or deletes a row.

The VBA code for hiding rows is:

Sub HideNthRow()
Dim rRange As Range, rCell As Range
Dim ln As Long, lCount As Long

ln = Application.InputBox(Prompt:="Hide Every:", Title:="Row
Hider", Default:=2, Type:=1)
If ln = 0 Then Exit Sub

Set rRange = Range("A1", Range("A65536").End(xlUp))

For lCount = ln To rRange.Rows.Count Step ln
rRange.Cells(lCount, 1).EntireRow.Hidden = True
Next lCount
End Sub

Works like a charm.

Thanks

Frank
 
witteman said:
I got help from the Excel newsgroup after all in the form of a simple
macro that hides every (nth) row or deletes a row.

The VBA code for hiding rows is:

Sub HideNthRow()
Dim rRange As Range, rCell As Range
Dim ln As Long, lCount As Long

ln = Application.InputBox(Prompt:="Hide Every:", Title:="Row
Hider", Default:=2, Type:=1)
If ln = 0 Then Exit Sub

Set rRange = Range("A1", Range("A65536").End(xlUp))

For lCount = ln To rRange.Rows.Count Step ln
rRange.Cells(lCount, 1).EntireRow.Hidden = True
Next lCount
End Sub

Works like a charm.

Thanks

Frank
Click to expand...

Great, but if you ever want to select random rows and (hide) or (delete) you
can do as I said for columns.

BTW, Do you think you will remember that VBA sequence off the top of your
head in a month?
 
Back
Top