deleting rows using X search and last row

  • Thread starter Thread starter jay
  • Start date Start date
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.
 
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
 
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
 
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.
 
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
 
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
 
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.
 
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?
 
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"
 
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
 
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.
 
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
 
Back
Top