Selecting specific rows in data range.

[BenSeekingHelp]

I have a large set of data in columns B thorugh H. Throughout the data
stream in columns B:H are tag names in column A named set 1, set 2 and so on.
I want to select and delete and shift up the data in row A through H only
between certain tags. e.g. delete the data in rows A:H from Set 3 up to but
not including set Set 4 and do the same from Set 7 up to set 8.

Any help would be greatly appreciated. Thanks!

 

[xl@lf]

I have a large set of data in columns B thorugh H.  Throughout the data
stream in columns B:H are tag names in column A named set 1, set 2 and soon.
 I want to select and delete and shift up the data in row A through H only
between certain tags.  e.g.  delete the data in rows A:H from Set 3 up to but
not including set Set 4 and do the same from Set 7 up to set 8.  

Any help would be greatly appreciated. Thanks!

Are you trying to do this with VBA code or manually on the spreadsheet?

 

[Ryan H]

What does your data look like in Col.A? We need to know what to test for.

Which example does it look like?

A A A
Set 3 3.0 3-1
Set 3 3.1 3-1
Set 4 4.7 4-7
Set 5 5.3 5-3
Set 7 6.0 5-0

 

[BenSeekingHelp]

Ryan, the dtat looks like

Column A B C D E F G H
Set 1 3 4 2 1 1 1
Set 2 3 1 1 1 1 1 Delete Row A:H
Set3 3 4 1 1 1 1
Set 4 1 1 1 1 1 1 Delete Row A:H
Etc.

Thanks

 

[BenSeekingHelp]

I am trying to do this with a macro. Thanks

Are you trying to do this with VBA code or manually on the spreadsheet?
.

 

[Ryan H]

I think you only want to delete "Set 3" and "Set 7", right? This code will
scan all values in Col. A looking for "Set 3" and "Set 7". If it finds it
will delete that rows A thru H. Hope this helps! If so, let me know, click
"YES" below.

Option Explicit

Sub DeleteRowTags()

Dim lngLastRow As Long
Dim rw As Long

With Sheets("Sheet1")

' find last row in Col. A
lngLastRow = .Cells(Rows.Count, "A").End(xlUp).Row


' scan each cell in Col. A
For rw = lngLastRow To 2 Step -1

' test for Set 3 or Set 7
If .Cells(rw, "A").Value = "Set 3" Or _
.Cells(rw, "A").Value = "Set 7" Then

' delete range A - H
.Range(.Cells(rw, "A"), .Cells(rw, "H")).Delete Shift:=xlUp
End If
Next rw
End With

End Sub

 

[BenSeekingHelp]

Ryan,
the logic doesn't seem to work. I have blank cells in column A betwen each
Set Name even though the data in B though H is continous. Do you think that
is impacting the code?

 

[Ryan H]

What do you mean the logic "doesn't seem to work"? Are you wanting to skip
the blank cells in Col. A? You failed to mention there were blanks in Col A.
This code will do the same as the last, but will skip the blanks. If this
doesn't work for you, please be specific in what you are wanting. Hope this
helps! If so, let me know, click "YES" below.

Option Explicit

Sub DeleteRowTags()

Dim lngLastRow As Long
Dim rw As Long

With Sheets("Sheet1")

' find last row in Col. A
lngLastRow = .Cells(Rows.Count, "A").End(xlUp).Row


' scan each cell in Col. A
For rw = lngLastRow To 2 Step -1

' test for Set 3 or Set 7
If Not IsEmpty(.Cells(rw, "A")) And _
(.Cells(rw, "A").Value = "Set 3" Or _
.Cells(rw, "A").Value = "Set 7") Then

' delete range A - H
.Range(.Cells(rw, "A"), .Cells(rw, "H")).Delete Shift:=xlUp
End If
Next rw
End With

End Sub

 

[OssieMac]

Hi Ben,

Have a look at my answer on your other post and see if it is doing what you
want.