Auto Deletion of certain Rows in a spreadsheet
 

I have a spreadsheet in which 4 of every 5 Rows need to be deleted. ie: Row
1 is good, Row 6 is good, Row 11 is good, Row 16 is good, etc. I need to
delete Rows 2-5, 7-10, 12-15, etc. I would prefer to set a variable in the
macro to tell it how many sets of 4 consecutive rows I need deleted (with one
good row between each bad set of 4). The rows that need to be deleted are not
entirely blank, some of the cells have data (not needed) but there are a few
rows that are entirely blank. It needs to delete the rows irreguardless of
any data in that row. Help is appreciated as I am not a programmer but you
guys are really good.

OR

Every 5th cell in column A has data. I need to delete all ROWS that have no
data in COLUMN A(even if it is a "space" that was used to delete previous
data) in column A. How would it know when it reached the end of the data and
continue to delete the balance of the blank spreadsheet?

Thanks
Danny

Auto Deletion of certain Rows in a spreadsheet
 

There was an earlier post using SumProduct to return every Nth item in a list
(that was pretty cool), but I couldn't find it. Programmatically - you could
use the following and copy it into a VBA module.

Select the column you want evaluated, run macro, and this should go through
and delete all but every 5th item.

Of course, back up your data in case this does not end up doing what you need.

Sub DeleteData()
Const N = 5
Dim SourceRange As Range
Dim RangeToDelete As Range

On Error Resume Next

Set SourceRange = Selection

If SourceRange.Rows.Count < 2 Or _
SourceRange.Columns.Count 1 Then Exit Sub

For Each X In SourceRange
If (X.Row - SourceRange.Cells(1, 1).Row) Mod N < 0 Then
If RangeToDelete Is Nothing Then
Set RangeToDelete = X
Else: Set RangeToDelete = Union(RangeToDelete, X)
End If
End If
Next X

RangeToDelete.EntireRow.Delete

End Sub


"scratching my head" wrote:

I have a spreadsheet in which 4 of every 5 Rows need to be deleted. ie: Row
1 is good, Row 6 is good, Row 11 is good, Row 16 is good, etc. I need to
delete Rows 2-5, 7-10, 12-15, etc. I would prefer to set a variable in the
macro to tell it how many sets of 4 consecutive rows I need deleted (with one
good row between each bad set of 4). The rows that need to be deleted are not
entirely blank, some of the cells have data (not needed) but there are a few
rows that are entirely blank. It needs to delete the rows irreguardless of
any data in that row. Help is appreciated as I am not a programmer but you
guys are really good.

OR

Every 5th cell in column A has data. I need to delete all ROWS that have no
data in COLUMN A(even if it is a "space" that was used to delete previous
data) in column A. How would it know when it reached the end of the data and
continue to delete the balance of the blank spreadsheet?

Thanks
Danny

Auto Deletion of certain Rows in a spreadsheet
 

A bit shorter, maybe not as generic

Sub Del_OneThroughFour()
Dim i As Long, Del_Count As Long
Const SkipRows = 4

For i = (SkipRows + 1) * Int(ActiveSheet.UsedRange.Count / (SkipRows + 1))
To (SkipRows + 1) Step -(SkipRows + 1)
ActiveSheet.Range("A" & i - 1, "A" & i - SkipRows).EntireRow.Delete
Del_Count = Del_Count + 1
Next i
'we might have left up to SkipRows extras not yet deleted - remove these now
ActiveSheet.Range("A" & Del_Count + 1, "A" & Del_Count + 5).EntireRow.Delete
End Sub



"JMB" wrote in message
...
There was an earlier post using SumProduct to return every Nth item in a
list
(that was pretty cool), but I couldn't find it. Programmatically - you
could
use the following and copy it into a VBA module.

Select the column you want evaluated, run macro, and this should go
through
and delete all but every 5th item.

Of course, back up your data in case this does not end up doing what you
need.

Sub DeleteData()
Const N = 5
Dim SourceRange As Range
Dim RangeToDelete As Range

On Error Resume Next

Set SourceRange = Selection

If SourceRange.Rows.Count < 2 Or _
SourceRange.Columns.Count 1 Then Exit Sub

For Each X In SourceRange
If (X.Row - SourceRange.Cells(1, 1).Row) Mod N < 0 Then
If RangeToDelete Is Nothing Then
Set RangeToDelete = X
Else: Set RangeToDelete = Union(RangeToDelete, X)
End If
End If
Next X

RangeToDelete.EntireRow.Delete

End Sub


"scratching my head" wrote:

I have a spreadsheet in which 4 of every 5 Rows need to be deleted. ie:
Row
1 is good, Row 6 is good, Row 11 is good, Row 16 is good, etc. I need to
delete Rows 2-5, 7-10, 12-15, etc. I would prefer to set a variable in
the
macro to tell it how many sets of 4 consecutive rows I need deleted (with
one
good row between each bad set of 4). The rows that need to be deleted are
not
entirely blank, some of the cells have data (not needed) but there are a
few
rows that are entirely blank. It needs to delete the rows irreguardless
of
any data in that row. Help is appreciated as I am not a programmer but
you
guys are really good.

OR

Every 5th cell in column A has data. I need to delete all ROWS that have
no
data in COLUMN A(even if it is a "space" that was used to delete previous
data) in column A. How would it know when it reached the end of the data
and
continue to delete the balance of the blank spreadsheet?

Thanks
Danny

Auto Deletion of certain Rows in a spreadsheet
 

When I run this, it doesn't seem to do anything?????
Any idea what I am doing wrong?
Thanks,
Danny

"JMB" wrote:

There was an earlier post using SumProduct to return every Nth item in a list
(that was pretty cool), but I couldn't find it. Programmatically - you could
use the following and copy it into a VBA module.

Select the column you want evaluated, run macro, and this should go through
and delete all but every 5th item.

Of course, back up your data in case this does not end up doing what you need.

Sub DeleteData()
Const N = 5
Dim SourceRange As Range
Dim RangeToDelete As Range

On Error Resume Next

Set SourceRange = Selection

If SourceRange.Rows.Count < 2 Or _
SourceRange.Columns.Count 1 Then Exit Sub

For Each X In SourceRange
If (X.Row - SourceRange.Cells(1, 1).Row) Mod N < 0 Then
If RangeToDelete Is Nothing Then
Set RangeToDelete = X
Else: Set RangeToDelete = Union(RangeToDelete, X)
End If
End If
Next X

RangeToDelete.EntireRow.Delete

End Sub


"scratching my head" wrote:

I have a spreadsheet in which 4 of every 5 Rows need to be deleted. ie: Row
1 is good, Row 6 is good, Row 11 is good, Row 16 is good, etc. I need to
delete Rows 2-5, 7-10, 12-15, etc. I would prefer to set a variable in the
macro to tell it how many sets of 4 consecutive rows I need deleted (with one
good row between each bad set of 4). The rows that need to be deleted are not
entirely blank, some of the cells have data (not needed) but there are a few
rows that are entirely blank. It needs to delete the rows irreguardless of
any data in that row. Help is appreciated as I am not a programmer but you
guys are really good.

OR

Every 5th cell in column A has data. I need to delete all ROWS that have no
data in COLUMN A(even if it is a "space" that was used to delete previous
data) in column A. How would it know when it reached the end of the data and
continue to delete the balance of the blank spreadsheet?

Thanks
Danny

Auto Deletion of certain Rows in a spreadsheet
 

When I try to run this I get a compile error in the line that starts with
"For i ="
Any idea what it is or if I am doing something wrong??
Thanks,
Danny

"William Benson" wrote:

A bit shorter, maybe not as generic

Sub Del_OneThroughFour()
Dim i As Long, Del_Count As Long
Const SkipRows = 4

For i = (SkipRows + 1) * Int(ActiveSheet.UsedRange.Count / (SkipRows + 1))
To (SkipRows + 1) Step -(SkipRows + 1)
ActiveSheet.Range("A" & i - 1, "A" & i - SkipRows).EntireRow.Delete
Del_Count = Del_Count + 1
Next i
'we might have left up to SkipRows extras not yet deleted - remove these now
ActiveSheet.Range("A" & Del_Count + 1, "A" & Del_Count + 5).EntireRow.Delete
End Sub



"JMB" wrote in message
...
There was an earlier post using SumProduct to return every Nth item in a
list
(that was pretty cool), but I couldn't find it. Programmatically - you
could
use the following and copy it into a VBA module.

Select the column you want evaluated, run macro, and this should go
through
and delete all but every 5th item.

Of course, back up your data in case this does not end up doing what you
need.

Sub DeleteData()
Const N = 5
Dim SourceRange As Range
Dim RangeToDelete As Range

On Error Resume Next

Set SourceRange = Selection

If SourceRange.Rows.Count < 2 Or _
SourceRange.Columns.Count 1 Then Exit Sub

For Each X In SourceRange
If (X.Row - SourceRange.Cells(1, 1).Row) Mod N < 0 Then
If RangeToDelete Is Nothing Then
Set RangeToDelete = X
Else: Set RangeToDelete = Union(RangeToDelete, X)
End If
End If
Next X

RangeToDelete.EntireRow.Delete

End Sub


"scratching my head" wrote:

I have a spreadsheet in which 4 of every 5 Rows need to be deleted. ie:
Row
1 is good, Row 6 is good, Row 11 is good, Row 16 is good, etc. I need to
delete Rows 2-5, 7-10, 12-15, etc. I would prefer to set a variable in
the
macro to tell it how many sets of 4 consecutive rows I need deleted (with
one
good row between each bad set of 4). The rows that need to be deleted are
not
entirely blank, some of the cells have data (not needed) but there are a
few
rows that are entirely blank. It needs to delete the rows irreguardless
of
any data in that row. Help is appreciated as I am not a programmer but
you
guys are really good.

OR

Every 5th cell in column A has data. I need to delete all ROWS that have
no
data in COLUMN A(even if it is a "space" that was used to delete previous
data) in column A. How would it know when it reached the end of the data
and
continue to delete the balance of the blank spreadsheet?

Thanks
Danny

Auto Deletion of certain Rows in a spreadsheet
 

did you use the mouse cursor to highlight/select the column you want the
macro to run on. it only runs on the range you have selected. If you have
only one cell selected, it will do nothing.

other than that not positive what the problem is. i tested it before
posting and it ran fine.

unless your VBA settings require variable declaration (i didn't declare the
x in my code-but not sure i need to as the macro runs on my computer either
way). you could check by clicking tools/macro/visual basic editor (Alt-F11),
go to Tools/Options/Editor and ensure "Require Variable Declaration" is
unchecked.


"scratching my head" wrote:

When I run this, it doesn't seem to do anything?????
Any idea what I am doing wrong?
Thanks,
Danny

"JMB" wrote:

There was an earlier post using SumProduct to return every Nth item in a list
(that was pretty cool), but I couldn't find it. Programmatically - you could
use the following and copy it into a VBA module.

Select the column you want evaluated, run macro, and this should go through
and delete all but every 5th item.

Of course, back up your data in case this does not end up doing what you need.

Sub DeleteData()
Const N = 5
Dim SourceRange As Range
Dim RangeToDelete As Range

On Error Resume Next

Set SourceRange = Selection

If SourceRange.Rows.Count < 2 Or _
SourceRange.Columns.Count 1 Then Exit Sub

For Each X In SourceRange
If (X.Row - SourceRange.Cells(1, 1).Row) Mod N < 0 Then
If RangeToDelete Is Nothing Then
Set RangeToDelete = X
Else: Set RangeToDelete = Union(RangeToDelete, X)
End If
End If
Next X

RangeToDelete.EntireRow.Delete

End Sub


"scratching my head" wrote:

I have a spreadsheet in which 4 of every 5 Rows need to be deleted. ie: Row
1 is good, Row 6 is good, Row 11 is good, Row 16 is good, etc. I need to
delete Rows 2-5, 7-10, 12-15, etc. I would prefer to set a variable in the
macro to tell it how many sets of 4 consecutive rows I need deleted (with one
good row between each bad set of 4). The rows that need to be deleted are not
entirely blank, some of the cells have data (not needed) but there are a few
rows that are entirely blank. It needs to delete the rows irreguardless of
any data in that row. Help is appreciated as I am not a programmer but you
guys are really good.

OR

Every 5th cell in column A has data. I need to delete all ROWS that have no
data in COLUMN A(even if it is a "space" that was used to delete previous
data) in column A. How would it know when it reached the end of the data and
continue to delete the balance of the blank spreadsheet?

Thanks
Danny

Auto Deletion of certain Rows in a spreadsheet
 

Hope you figured out that pasting the code in the forum message caused it to
shift down, sorry.
"scratching my head" wrote in
message ...
When I try to run this I get a compile error in the line that starts with
"For i ="
Any idea what it is or if I am doing something wrong??
Thanks,
Danny

"William Benson" wrote:

A bit shorter, maybe not as generic

Sub Del_OneThroughFour()
Dim i As Long, Del_Count As Long
Const SkipRows = 4

For i = (SkipRows + 1) * Int(ActiveSheet.UsedRange.Count / (SkipRows +
1))
To (SkipRows + 1) Step -(SkipRows + 1)
ActiveSheet.Range("A" & i - 1, "A" & i - SkipRows).EntireRow.Delete
Del_Count = Del_Count + 1
Next i
'we might have left up to SkipRows extras not yet deleted - remove these
now
ActiveSheet.Range("A" & Del_Count + 1, "A" & Del_Count +
5).EntireRow.Delete
End Sub



"JMB" wrote in message
...
There was an earlier post using SumProduct to return every Nth item in
a
list
(that was pretty cool), but I couldn't find it. Programmatically - you
could
use the following and copy it into a VBA module.

Select the column you want evaluated, run macro, and this should go
through
and delete all but every 5th item.

Of course, back up your data in case this does not end up doing what
you
need.

Sub DeleteData()
Const N = 5
Dim SourceRange As Range
Dim RangeToDelete As Range

On Error Resume Next

Set SourceRange = Selection

If SourceRange.Rows.Count < 2 Or _
SourceRange.Columns.Count 1 Then Exit Sub

For Each X In SourceRange
If (X.Row - SourceRange.Cells(1, 1).Row) Mod N < 0 Then
If RangeToDelete Is Nothing Then
Set RangeToDelete = X
Else: Set RangeToDelete = Union(RangeToDelete, X)
End If
End If
Next X

RangeToDelete.EntireRow.Delete

End Sub


"scratching my head" wrote:

I have a spreadsheet in which 4 of every 5 Rows need to be deleted.
ie:
Row
1 is good, Row 6 is good, Row 11 is good, Row 16 is good, etc. I need
to
delete Rows 2-5, 7-10, 12-15, etc. I would prefer to set a variable in
the
macro to tell it how many sets of 4 consecutive rows I need deleted
(with
one
good row between each bad set of 4). The rows that need to be deleted
are
not
entirely blank, some of the cells have data (not needed) but there are
a
few
rows that are entirely blank. It needs to delete the rows
irreguardless
of
any data in that row. Help is appreciated as I am not a programmer
but
you
guys are really good.

OR

Every 5th cell in column A has data. I need to delete all ROWS that
have
no
data in COLUMN A(even if it is a "space" that was used to delete
previous
data) in column A. How would it know when it reached the end of the
data
and
continue to delete the balance of the blank spreadsheet?

Thanks
Danny

Auto Deletion of certain Rows in a spreadsheet
 

I think he was referring to my code - I pasted his answer, way too late now
I am sure.

"JMB" wrote in message
...
did you use the mouse cursor to highlight/select the column you want the
macro to run on. it only runs on the range you have selected. If you
have
only one cell selected, it will do nothing.

other than that not positive what the problem is. i tested it before
posting and it ran fine.

unless your VBA settings require variable declaration (i didn't declare
the
x in my code-but not sure i need to as the macro runs on my computer
either
way). you could check by clicking tools/macro/visual basic editor
(Alt-F11),
go to Tools/Options/Editor and ensure "Require Variable Declaration" is
unchecked.


"scratching my head" wrote:

When I run this, it doesn't seem to do anything?????
Any idea what I am doing wrong?
Thanks,
Danny

"JMB" wrote:

There was an earlier post using SumProduct to return every Nth item in
a list
(that was pretty cool), but I couldn't find it. Programmatically - you
could
use the following and copy it into a VBA module.

Select the column you want evaluated, run macro, and this should go
through
and delete all but every 5th item.

Of course, back up your data in case this does not end up doing what
you need.

Sub DeleteData()
Const N = 5
Dim SourceRange As Range
Dim RangeToDelete As Range

On Error Resume Next

Set SourceRange = Selection

If SourceRange.Rows.Count < 2 Or _
SourceRange.Columns.Count 1 Then Exit Sub

For Each X In SourceRange
If (X.Row - SourceRange.Cells(1, 1).Row) Mod N < 0 Then
If RangeToDelete Is Nothing Then
Set RangeToDelete = X
Else: Set RangeToDelete = Union(RangeToDelete, X)
End If
End If
Next X

RangeToDelete.EntireRow.Delete

End Sub


"scratching my head" wrote:

I have a spreadsheet in which 4 of every 5 Rows need to be deleted.
ie: Row
1 is good, Row 6 is good, Row 11 is good, Row 16 is good, etc. I need
to
delete Rows 2-5, 7-10, 12-15, etc. I would prefer to set a variable
in the
macro to tell it how many sets of 4 consecutive rows I need deleted
(with one
good row between each bad set of 4). The rows that need to be deleted
are not
entirely blank, some of the cells have data (not needed) but there
are a few
rows that are entirely blank. It needs to delete the rows
irreguardless of
any data in that row. Help is appreciated as I am not a programmer
but you
guys are really good.

OR

Every 5th cell in column A has data. I need to delete all ROWS that
have no
data in COLUMN A(even if it is a "space" that was used to delete
previous
data) in column A. How would it know when it reached the end of the
data and
continue to delete the balance of the blank spreadsheet?

Thanks
Danny