Delete Rows
I would like to write code that would find text in column A and then select
all rows to the last row, and finally delete all the selected rows including the original row where the text was found. I am able to find the text, no problem, and the text is unique to the first column. I used ActiveCell.End(xlDown) to get to the last row. But, it doesn't select all the rows. I tried finding the text and then using: strBeginCell = Selection.Addres as the beginning address, then using the following to get the ending cell address: ActiveCell.End(xlDown).Select strEndCell = Selection.Address And, finally using the following to select all the rows, at which point I get a syntax error: Range(strBegCell:strEndCell).Select Thank you. -- DougW |
Delete Rows
Range(strBegCell & ":" & strEndCell).Select
Hth, Merjet |
Delete Rows
Sub delColA()
lastRow = Cells(Rows.Count, 1).End(xlUp).Row If Range("A1") = "" And Range("A2") = "" Then x = Range("A1').End(xlDown).Row Else x = Range("A2").Row End If Range("A" & x & ":A" & lastRow).Delete End Sub "DougW" wrote: I would like to write code that would find text in column A and then select all rows to the last row, and finally delete all the selected rows including the original row where the text was found. I am able to find the text, no problem, and the text is unique to the first column. I used ActiveCell.End(xlDown) to get to the last row. But, it doesn't select all the rows. I tried finding the text and then using: strBeginCell = Selection.Addres as the beginning address, then using the following to get the ending cell address: ActiveCell.End(xlDown).Select strEndCell = Selection.Address And, finally using the following to select all the rows, at which point I get a syntax error: Range(strBegCell:strEndCell).Select Thank you. -- DougW |
Delete Rows
Actually, if you don't have any data in column A that you want to save, you
could just use: Columns("A").ClearContents "DougW" wrote: I would like to write code that would find text in column A and then select all rows to the last row, and finally delete all the selected rows including the original row where the text was found. I am able to find the text, no problem, and the text is unique to the first column. I used ActiveCell.End(xlDown) to get to the last row. But, it doesn't select all the rows. I tried finding the text and then using: strBeginCell = Selection.Addres as the beginning address, then using the following to get the ending cell address: ActiveCell.End(xlDown).Select strEndCell = Selection.Address And, finally using the following to select all the rows, at which point I get a syntax error: Range(strBegCell:strEndCell).Select Thank you. -- DougW |
Delete Rows
Maybe
Sub standard() Dim myrange, MyRange1 As Range LastRow = Cells(Rows.Count, "A").End(xlUp).Row Set myrange = Sheets("Sheet1").Range("A1:A" & LastRow) For Each c In myrange If UCase(c.Value) = "TEST" Then 'Change you your string LastRow = Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious).Row Range("A" & c.Row & ":A" & LastRow).EntireRow.ClearContents End If Next End Sub Mike "DougW" wrote: I would like to write code that would find text in column A and then select all rows to the last row, and finally delete all the selected rows including the original row where the text was found. I am able to find the text, no problem, and the text is unique to the first column. I used ActiveCell.End(xlDown) to get to the last row. But, it doesn't select all the rows. I tried finding the text and then using: strBeginCell = Selection.Addres as the beginning address, then using the following to get the ending cell address: ActiveCell.End(xlDown).Select strEndCell = Selection.Address And, finally using the following to select all the rows, at which point I get a syntax error: Range(strBegCell:strEndCell).Select Thank you. -- DougW |
Delete Rows
You can use this subroutine to find the word in Column A and then delete
that row on down... Sub FindAndDeleteDown(WordToFind As String) Dim LastRow As Long Dim StartRow As Long With Worksheets("Sheet5") LastRow = .Cells(Rows.Count, "A").End(xlUp).Row For StartRow = 1 To LastRow + 1 If StrComp(.Cells(StartRow, "A").Value, _ WordToFind, vbTextCompare) = 0 Then Exit For Next If StartRow < LastRow + 1 Then .Rows(StartRow & ":" & LastRow).Delete End With End Sub Just call this subroutine from within your own passing the word you want to find as its argument. For example... SubTest() FindAndDeleteDown "My Word" End Sub Rick "DougW" wrote in message ... I would like to write code that would find text in column A and then select all rows to the last row, and finally delete all the selected rows including the original row where the text was found. I am able to find the text, no problem, and the text is unique to the first column. I used ActiveCell.End(xlDown) to get to the last row. But, it doesn't select all the rows. I tried finding the text and then using: strBeginCell = Selection.Addres as the beginning address, then using the following to get the ending cell address: ActiveCell.End(xlDown).Select strEndCell = Selection.Address And, finally using the following to select all the rows, at which point I get a syntax error: Range(strBegCell:strEndCell).Select Thank you. -- DougW |
Delete Rows
A simple answer would be to resize using;
ActiveCell.Resize(x, y).Select With x being the number of rows and y being the number of columns. Followed by: Selection.ClearContents Depending on whether you want to just clear, or Selection.Delete Shift:=xlUp if you want to delete. "DougW" wrote: I would like to write code that would find text in column A and then select all rows to the last row, and finally delete all the selected rows including the original row where the text was found. I am able to find the text, no problem, and the text is unique to the first column. I used ActiveCell.End(xlDown) to get to the last row. But, it doesn't select all the rows. I tried finding the text and then using: strBeginCell = Selection.Addres as the beginning address, then using the following to get the ending cell address: ActiveCell.End(xlDown).Select strEndCell = Selection.Address And, finally using the following to select all the rows, at which point I get a syntax error: Range(strBegCell:strEndCell).Select Thank you. -- DougW |
All times are GMT +1. The time now is 02:26 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com