Command button macro operation

HALENHARDY

New Member
Joined
May 13, 2005
Messages
3
I'm not too good at explaining things but I have a VB program that I made to do some line deleting. It works as I want it to but I would like to be able to build a Command button routine to do the same thing. I get a "Select Method of Range Class Failed" message when trying to delete a line from a second worksheet. It deletes lines OK in the main worksheet using the same command. I probably need to attach the Workbook so you can see what i"m doing, But I don't know how. All help appreciated. Thanks Gary Hardy
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
Hi,
Welcome to the board.
Check whether the second sheet (or the range of cells) is protected.
Just post your code that you use now.
 
Upvote 0
Code:
Private Sub CommandButton1_Click()
 Dim WRK1(100), WRK2(100), WRK3(100)
  For I = 1 To 100: WRK1(I) = "": Next I
   
  Worksheets("MAR").Activate
'********DEBUG SETUPOFWORKSHEET********************
N = 1001
For I = 3 To 6
  ActiveSheet.Cells(I, 1) = N
  ActiveSheet.Cells(I, 2) = "TP"
  N = N + 1
Next I

 ActiveSheet.Cells(3, 3) = "@"
 ActiveSheet.Cells(4, 3) = 5
 ActiveSheet.Cells(5, 3) = "@"
 ActiveSheet.Cells(6, 3) = 10
 
'********END OF DEBUG SETUP****************************
 
'***************************************************************************************
'            DELETE ROWS THAT HAVE A "@" IN COL 3
'***************************************************************************************
  I = 1
  For Row = 3 To 6
    If ActiveSheet.Cells(Row, 3) = "@" Then
       Rows(Row).Select
        Selection.Delete SHIFT:=xlUp
         Row = Row - 1
      Else
       If ActiveSheet.Cells(Row, 1) = "" Then Exit For  'END OF LIST
    End If
  Next Row
  
  Row = 3
  For I = 1 To 4
   WRK1(I) = ActiveSheet.Cells(Row, 1).Value 'SAVE NUMBERS
  Next I
  
  
'***************************************************************************************
'DELETE  ROWS THAT HAVE THE SAME NUMBER
'***************************************************************************************
  Worksheets("APR_RE").Activate
 
  I = 1
  For Row = 3 To 6
   N = ActiveSheet.Cells(Row, 1).Value
     If N = WRK1(I) Then
      Rows(Row).Select     'this is where i get the failure
       Selection.Delete SHIFT:=xlUp
        Row = Row - 1
          I = I + 1
      End If
   Next Row


end sub
'*******************below is what is the apr_re worksheet*
      1001	TP 1	@
      1002	TP 2	@
      1003	TP 3	@
      1004	TP 4	@
 
Upvote 0

Forum statistics

Threads
1,221,638
Messages
6,160,994
Members
451,682
Latest member
ogoreo

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top