ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Find and Delete (https://www.excelbanter.com/excel-programming/425650-find-delete.html)

Little Penny[_3_]
Find and Delete
 
I want to find a value in colum "B" and delete all row above except
the first row.

I tried to adapt the code below without success.



Option Explicit

Sub delete_row_and_below()

Dim rFound As Range
Dim myRange As Range
Dim myLastRow As Long
Dim sName As String

myLastRow = ActiveSheet.Cells(65000, 2).End(xlUp).Row
Set myRange = ActiveSheet.Range("B1:B" & myLastRow)
sName = "LI-HANDTMG"

Set rFound = myRange.Find(What:=sName, _
LookIn:=xlValues, _
LookAt:=xlWhole, _
MatchCase:=False)

If rFound Is Nothing Then
MsgBox sName & " was not found in Range."
Else
Set myRange = ActiveSheet.Range("b" & rFound.Row _
& ":f" & myLastRow)
myRange.EntireRow.Delete
End If


End Sub



Thanks Little Penny

JLGWhiz
Find and Delete
 
Hi Little Penny, did you not want this:

Set myRange = ActiveSheet.Range("b" & rFound.Row _
& ":f" & myLastRow)

to be more like this?:

Set myRange = ActiveSheet.Range("b" & rFound.Row _
& ":f" & rFound.Row)


"Little Penny" wrote:

I want to find a value in colum "B" and delete all row above except
the first row.

I tried to adapt the code below without success.



Option Explicit

Sub delete_row_and_below()

Dim rFound As Range
Dim myRange As Range
Dim myLastRow As Long
Dim sName As String

myLastRow = ActiveSheet.Cells(65000, 2).End(xlUp).Row
Set myRange = ActiveSheet.Range("B1:B" & myLastRow)
sName = "LI-HANDTMG"

Set rFound = myRange.Find(What:=sName, _
LookIn:=xlValues, _
LookAt:=xlWhole, _
MatchCase:=False)

If rFound Is Nothing Then
MsgBox sName & " was not found in Range."
Else
Set myRange = ActiveSheet.Range("b" & rFound.Row _
& ":f" & myLastRow)
myRange.EntireRow.Delete
End If


End Sub



Thanks Little Penny

JLGWhiz
Find and Delete
 
Sorry, you said all above except first row. I am assuming you also want to
delete the found row. If not, just change to rFound - 1.

Set myRange = ActiveSheet.Range("b2" & ":f" & rFound.Row)

If you want to include column A in the delete, change b2 to A2.

To delete the entire row:

Set myRange = ActiveSheet.Range("b2" & ":b" & rFound.Row)
myRange.EntireRow.Delete



"Little Penny" wrote:

I want to find a value in colum "B" and delete all row above except
the first row.

I tried to adapt the code below without success.



Option Explicit

Sub delete_row_and_below()

Dim rFound As Range
Dim myRange As Range
Dim myLastRow As Long
Dim sName As String

myLastRow = ActiveSheet.Cells(65000, 2).End(xlUp).Row
Set myRange = ActiveSheet.Range("B1:B" & myLastRow)
sName = "LI-HANDTMG"

Set rFound = myRange.Find(What:=sName, _
LookIn:=xlValues, _
LookAt:=xlWhole, _
MatchCase:=False)

If rFound Is Nothing Then
MsgBox sName & " was not found in Range."
Else
Set myRange = ActiveSheet.Range("b" & rFound.Row _
& ":f" & myLastRow)
myRange.EntireRow.Delete
End If


End Sub



Thanks Little Penny

Little Penny[_3_]
Find and Delete
 
Thank for yor reply. I tried the chages but it does not work. So I
started over. I got the macro to find the value and select the the
entire row above. How can i select all row up to row 2.


Cells.Find(What:="Ground", After:=ActiveCell, LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows,
SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate

ActiveCell.Offset(-1, 0).Select

Selection.EntireRow.Select


I need to select up to row 2



Thanks

On Mon, 16 Mar 2009 19:47:01 -0700, JLGWhiz
wrote:

Hi Little Penny, did you not want this:

Set myRange = ActiveSheet.Range("b" & rFound.Row _
& ":f" & myLastRow)

to be more like this?:

Set myRange = ActiveSheet.Range("b" & rFound.Row _
& ":f" & rFound.Row)


"Little Penny" wrote:

I want to find a value in colum "B" and delete all row above except
the first row.

I tried to adapt the code below without success.



Option Explicit

Sub delete_row_and_below()

Dim rFound As Range
Dim myRange As Range
Dim myLastRow As Long
Dim sName As String

myLastRow = ActiveSheet.Cells(65000, 2).End(xlUp).Row
Set myRange = ActiveSheet.Range("B1:B" & myLastRow)
sName = "LI-HANDTMG"

Set rFound = myRange.Find(What:=sName, _
LookIn:=xlValues, _
LookAt:=xlWhole, _
MatchCase:=False)

If rFound Is Nothing Then
MsgBox sName & " was not found in Range."
Else
Set myRange = ActiveSheet.Range("b" & rFound.Row _
& ":f" & myLastRow)
myRange.EntireRow.Delete
End If


End Sub



Thanks Little Penny

JLGWhiz
Find and Delete
 
There was nothing wrong with the first procedure that you posted, ecept the
line for the variable myRange. After reading your post again, it looks like
what you want to use is:

Set myRange = ActiveSheet.Range("b2:b" & rFound.Row)
myRange.EntireRow.Delete

This will delete rows 2 through the found row.


"Little Penny" wrote:

Thank for yor reply. I tried the chages but it does not work. So I
started over. I got the macro to find the value and select the the
entire row above. How can i select all row up to row 2.


Cells.Find(What:="Ground", After:=ActiveCell, LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows,
SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate

ActiveCell.Offset(-1, 0).Select

Selection.EntireRow.Select


I need to select up to row 2



Thanks

On Mon, 16 Mar 2009 19:47:01 -0700, JLGWhiz
wrote:

Hi Little Penny, did you not want this:

Set myRange = ActiveSheet.Range("b" & rFound.Row _
& ":f" & myLastRow)

to be more like this?:

Set myRange = ActiveSheet.Range("b" & rFound.Row _
& ":f" & rFound.Row)


"Little Penny" wrote:

I want to find a value in colum "B" and delete all row above except
the first row.

I tried to adapt the code below without success.



Option Explicit

Sub delete_row_and_below()

Dim rFound As Range
Dim myRange As Range
Dim myLastRow As Long
Dim sName As String

myLastRow = ActiveSheet.Cells(65000, 2).End(xlUp).Row
Set myRange = ActiveSheet.Range("B1:B" & myLastRow)
sName = "LI-HANDTMG"

Set rFound = myRange.Find(What:=sName, _
LookIn:=xlValues, _
LookAt:=xlWhole, _
MatchCase:=False)

If rFound Is Nothing Then
MsgBox sName & " was not found in Range."
Else
Set myRange = ActiveSheet.Range("b" & rFound.Row _
& ":f" & myLastRow)
myRange.EntireRow.Delete
End If


End Sub



Thanks Little Penny

Little Penny[_3_]
Find and Delete
 

That worked

Thanks






On Mon, 16 Mar 2009 20:16:01 -0700, JLGWhiz
wrote:

There was nothing wrong with the first procedure that you posted, ecept the
line for the variable myRange. After reading your post again, it looks like
what you want to use is:

Set myRange = ActiveSheet.Range("b2:b" & rFound.Row)
myRange.EntireRow.Delete

This will delete rows 2 through the found row.


"Little Penny" wrote:

Thank for yor reply. I tried the chages but it does not work. So I
started over. I got the macro to find the value and select the the
entire row above. How can i select all row up to row 2.


Cells.Find(What:="Ground", After:=ActiveCell, LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows,
SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate

ActiveCell.Offset(-1, 0).Select

Selection.EntireRow.Select


I need to select up to row 2



Thanks

On Mon, 16 Mar 2009 19:47:01 -0700, JLGWhiz
wrote:

Hi Little Penny, did you not want this:

Set myRange = ActiveSheet.Range("b" & rFound.Row _
& ":f" & myLastRow)

to be more like this?:

Set myRange = ActiveSheet.Range("b" & rFound.Row _
& ":f" & rFound.Row)


"Little Penny" wrote:

I want to find a value in colum "B" and delete all row above except
the first row.

I tried to adapt the code below without success.



Option Explicit

Sub delete_row_and_below()

Dim rFound As Range
Dim myRange As Range
Dim myLastRow As Long
Dim sName As String

myLastRow = ActiveSheet.Cells(65000, 2).End(xlUp).Row
Set myRange = ActiveSheet.Range("B1:B" & myLastRow)
sName = "LI-HANDTMG"

Set rFound = myRange.Find(What:=sName, _
LookIn:=xlValues, _
LookAt:=xlWhole, _
MatchCase:=False)

If rFound Is Nothing Then
MsgBox sName & " was not found in Range."
Else
Set myRange = ActiveSheet.Range("b" & rFound.Row _
& ":f" & myLastRow)
myRange.EntireRow.Delete
End If


End Sub



Thanks Little Penny


All times are GMT +1. The time now is 02:49 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com