Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with code problem
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with code problem
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. "Mekinnik" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with code problem
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). -- Regards, Bill Renaud |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with code problem
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 -- Regards, Tom Ogilvy "Mekinnik" wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with code problem
Yep, I missed that. Too late at night I guess.
"Bill Renaud" wrote: 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). -- Regards, Bill Renaud |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with code problem
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. "Tom Ogilvy" wrote: 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 -- Regards, Tom Ogilvy "Mekinnik" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Problem with code | Excel Programming | |||
Problem with code | Excel Programming | |||
Help with code problem | Excel Programming | |||
vba code problem | Excel Programming | |||
Code Problem ! | Excel Programming |