Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete Rows
Range(strBegCell & ":" & strEndCell).Select
Hth, Merjet |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Delete Rows if any cell in Column H is blank but do not Delete Fir | Excel Programming | |||
Copy pasting Rows, but need to Delete any Shapes/Pictures that are within copied rows | Excel Programming | |||
Cut filtered rows, paste into next empty row of new sheet, and delete cut rows | Excel Worksheet Functions | |||
How to delete rows when List toolbar's "delete" isnt highlighted? | Excel Worksheet Functions | |||
Delete every 3rd row, then delete rows 2-7, move info f/every 2nd row up one to the end and delete the row below | Excel Programming |