Selecting specific rows in data range.

B

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!
 
X

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?
 
R

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
 
B

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
 
R

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
 
B

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?
 
R

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
 
O

OssieMac

Hi Ben,

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top