Help with code problem

[Mekinnik]

Why will the following code not delete column A in addition to the other
columns?

Dim fRow As Long
On Error GoTo ender
fRow = Columns(2).Find(What:=txt1.value, _
After:=Cells(1, 2), LookIn: =xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False, _
SearchFormat:=False).Row
Rows(fRow).Delete
Exit Sub
ender:
MsgBox "Value not found"
End Sub

 

[JLGWhiz]

Mostly because the code does not tell it to delete column A. fRow does not
equate to an iteger according to your code. You have it equal to a
text.value. If you want to delet rows then you use a row reference. If you
want to delete a column, you use a column reference. You can do it like:
Range("B6").EntireRow.Delete 'Deletes row 6

or

Range("B6").EntireColumn.Delete 'Deletes Column B

Or

Rows(6).Delete

Or

Columns("B").Delete

If you use a variable, then the variable must equal one of the above cell
type or row or column references.

 

[Bill Renaud]

JLGWhiz wrote:
<<You have it equal to a text.value.>>

He has .Row on the end of the Find method.

Also, Rows(fRow).Delete does, in fact, refer to an entire row, since there
is no qualifier on the front. Therefore, it is equivalent to:

ActiveSheet.Rows(fRow).Delete

and therefore, it does actually delete the entire row (in my testing), once
it finds the value.

I would suggest adding object variables to the routine for both Columns(2)
and the result of the Find method, then stepping through the code to verify
what it is doing. I can't run his code exactly, since I am still back on
Excel 2000, and I do not have the SearchFormat:=False parameter available
(I had to delete it when I was testing).

 

[Tom Ogilvy]

You are saying your are deleting an entirerow, but the cell of that row in
column A is not deleted - I couldn't reproduce that, even with column A
hidden. The code worked fine for me: xl2003

 

[JLGWhiz]

Yep, I missed that. Too late at night I guess.

 

[Mekinnik]

This is what I have. I have a user form that the user has to enter names in
and the add button works fine it copies all the textbox infor to sheet
'MANCODE' which gets sorted by a worksheet change event also the change event
code numbers the entries an assigns the next higher number to column A of
that row before it sorts column B which happens to be where textbox 'TxtMan'
gets sent to. Now what I want to do is when the user clicks the enters a name
in the TxtMan textbox and clicks delete it will seach column B for the name
and then clears all the contents of the row including row A. I also need the
click event to resort and assign new numbers after it deletes the row.