deleting rows using X search and last row

J

jay

I have a column H that has numbers in it.
I want to find down the column for an X.
When the X is found I need to select that row
that X is in and select the last row with data and
delete the selected rows.
 
F

Frank Kabel

Hi
try the following macro:
Sub delete_rows()
Dim lastrow As Long
Dim row_index As Long
Application.ScreenUpdating = False
lastrow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
For row_index = 1 to lastrow
If Cells(row_index, "A").Value= " X" then
Rows(row_index & ":" & lastrow).delete
exit for
End If
Next
Application.ScreenUpdating = True
End Sub
 
D

Don Guillett

If I understand your request, this will delete rows from where x is found in
H to include the last cell in H with data.
If x at row 13 and 1000 is in cell h20 then those rows will be deleted.
Selections are NOT necessary.

Sub deleterng()
with sheets("sheet3")
fx = .Columns("H").Find("X").Row
lr = .Cells(Rows.Count, "H").End(xlUp).Row
..Rows(fx & ":" & lr).Delete
end with
End Sub
---
If on the sheet in question, then
Sub deleterngA()
fx = Columns("H").Find("X").Row
lr = Cells(Rows.Count, "H").End(xlUp).Row
Rows(fx & ":" & lr).Delete
End Sub
 
J

jay

Frank said:
Hi
try the following macro:
Sub delete_rows()
Dim lastrow As Long
Dim row_index As Long
Application.ScreenUpdating = False
lastrow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
For row_index = 1 to lastrow
If Cells(row_index, "A").Value= " X" then
Rows(row_index & ":" & lastrow).delete
exit for
End If
Next
Application.ScreenUpdating = True
End Sub
Click to expand...


Not working it does not delete any rows with X in column H.

Is there a way to find down the H column find the first X then
select all rows between X and the last row and delete them.
I have about 4000 rows with Xs in column H.
I can do it with the mouse. I just first select the first X and
goto the last row and shift hold and select all several rows and delete.
 
F

Frank Kabel

Hi
just change the column index:
Sub delete_rows()
Dim lastrow As Long
Dim row_index As Long
Application.ScreenUpdating = False
lastrow = ActiveSheet.Cells(Rows.Count, "H").End(xlUp).Row
For row_index = 1 to lastrow
If Cells(row_index, "H").Value= " X" then
Rows(row_index & ":" & lastrow).delete
exit for
End If
Next
Application.ScreenUpdating = True
End Sub
 
J

JE McGimpsey

one way:

Public Sub DeleteXinH()
Dim rDelete As Range
Dim rCell As Range

For Each rCell In Range("H1:H" & _
Range("H" & Rows.Count).End(xlUp).Row)
With rCell
If .Value = "X" Then
If rDelete Is Nothing Then
Set rDelete = .Cells
Else
Set rDelete = Union(rDelete, .Cells)
End If
End If
End With
Next rCell
If Not rDelete Is Nothing Then rDelete.EntireRow.Delete
End Sub
 
J

jay

Frank said:
Hi
just change the column index:
Sub delete_rows()
Dim lastrow As Long
Dim row_index As Long
Application.ScreenUpdating = False
lastrow = ActiveSheet.Cells(Rows.Count, "H").End(xlUp).Row
For row_index = 1 to lastrow
If Cells(row_index, "H").Value= " X" then
Rows(row_index & ":" & lastrow).delete
exit for
End If
Next
Application.ScreenUpdating = True
End Sub
Click to expand...

It deleted the cells with X in the Column H, but it did not
delete the rows.
Any Ideas why?
Almost there.
 
F

Frank Kabel

Hi
just testedt it again and it deleted the entire rows. You may remove a
blank in the line
If Cells(row_index, "H").Value= " X" then

in the test criteria (before the X). It should be
If Cells(row_index, "H").Value= "X" then

What is the exact code you have used?
 
J

jay

Don said:
If I understand your request, this will delete rows from where x is found
in H to include the last cell in H with data.
If x at row 13 and 1000 is in cell h20 then those rows will be deleted.
Selections are NOT necessary.

Sub deleterng()
with sheets("sheet3")
fx = .Columns("H").Find("X").Row
lr = .Cells(Rows.Count, "H").End(xlUp).Row
.Rows(fx & ":" & lr).Delete
end with
End Sub
---
If on the sheet in question, then
Sub deleterngA()
fx = Columns("H").Find("X").Row
lr = Cells(Rows.Count, "H").End(xlUp).Row
Rows(fx & ":" & lr).Delete
End Sub
Click to expand...


Hi. The another code procedure below is working. The H column has let's
say 300 x's down the column. However there are more data in the G column
that goes to 2000
records. So, I just encluded an If statement for that G column and it
deleted them.
I will play around with your first code procedure. I think it may be a
simpler method if I can get it to work with the G column. Maybe it will
delete the whole records in one sweep.

Here is what is going on.
Column H has the x's in about 300 records. I need to find the first x.
Next, Column G has data upto 2000 records with no x's in column H.
So, I need to find the last row in column G.
The goal is to delete the first row with the first x and all other rows to
the last row in the G column.

A B C D E F G H
CCC C C C C C C SSS
SDF SW FS FFFG S F SSA DS
CDS SD FD SSS A A ASS X
DSS C S CCC F F EFE X
CCC C C C C C C
SDF SW FS FFFG S F SSA
SDF SW FS FFFG S F SSA
SDF SW FS FFFG S F SSA

I need to delete the row with the first X in H column and all other rows to
the last cell in column G.

1 find the first x in column H
2 find the last cell with data in column G
3 delete all records in between.

It can be pretty hard trying to explain the whole picture of what is going
on.
I appreciate your help. Thank You.

Maybe replacing the H in the lr to a G will do it. As in
"lr = .Cells(Rows.Count, "G").End(xlUp).Row"
 
D

Don Guillett

It will since it FINDS the first X, marks it and then finds the last row,
marks it and then deletes.
for the last row in col G, just change H to G in the lr= line
 
J

jay

Frank said:
Hi
just testedt it again and it deleted the entire rows. You may remove a
blank in the line
If Cells(row_index, "H").Value= " X" then

in the test criteria (before the X). It should be
If Cells(row_index, "H").Value= "X" then

What is the exact code you have used?
Click to expand...





Dim lastrow As Long
Dim row_index As Long
Application.ScreenUpdating = False
lastrow = ActiveSheet.Cells(Rows.Count, "H").End(xlUp).Row
For row_index = 1 To lastrow
If Cells(row_index, "H").Value = "X" Then
Rows(row_index & ":" & lastrow).Delete
Exit For
End If
lastrow = ActiveSheet.Cells(Rows.Count, "G").End(xlUp).Row
If Cells(row_index, "G").Value = "u" Then
Rows(row_index & ":" & lastrow).Delete
Exit For
End If

Next

I used u to search in column G.
 
F

Frank Kabel

Hi
make this
Dim lastrow As Long
Dim row_index As Long
Application.ScreenUpdating = False
lastrow = ActiveSheet.Cells(Rows.Count, "H").End(xlUp).Row
For row_index = 1 To lastrow
If Cells(row_index, "H").Value = "X" Then
Rows(row_index & ":" & lastrow).Delete
Exit For
End If
next

lastrow = ActiveSheet.Cells(Rows.Count, "G").End(xlUp).Row
For row_index = 1 To lastrow
If Cells(row_index, "G").Value = "u" Then
Rows(row_index & ":" & lastrow).Delete
Exit For
End If

Next
 
Top