Where To Put Code

[doctortt]

I'm having trouble with the codes below. When I execute below, nothing happens.

iFindCol = 0iFindCol = Application.WorksheetFunction.Match("Milestone", Sheets("Report Data").Rows(1), 0)
For z = 2 To Sheets("Report Data").Cells(Rows.Count, "A").End(xlUp).Row
If Sheets("Report Data").Cells(z, iFindCol).Value = "Withdrawal" Then
Rows(z).EntireRow.Delete
End If
Next z

[doctortt]

How come I can't post the codes properly?

[Paul_Hossler]

1. I don't know why you have problems with the codes

2. Might be the space between the "[" and the "C" in the [ CODE]

iFindCol = 0
iFindCol = Application.WorksheetFunction.Match("Milestone", Sheets("Report Data").Rows(1), 0)
 For z = 2 To Sheets("Report Data").Cells(Rows.Count, "A").End(xlUp).Row
      If Sheets("Report Data").Cells(z, iFindCol).Value = "Withdrawal" Then
          Rows(z).EntireRow.Delete
     End If
Next z

To delete rows, you need to start from the bottom and delete your way up

For iRow = Activesheet.UsedRange.Rows.Count To 2 Step -1
    If .... Then ActiveSheet.Rows (iRow).Delete
Next iRow

Paul

[Trebor76]

How come I can't post the codes properly?

You have spaces in the tags - remove the spaces from within the square brackets and all should be fine.

Robert

[doctortt]

I'm still having trouble with the codes. Can someone please help? I don't want to use the activesheet code because I'd like to have all codes for this Excel workbook on one single worksheet

iFindCol = 0
iFindCol = Application.WorksheetFunction.Match("Milestone", Sheets("Report Data").Rows(1), 0)
For z = Sheets("Report Data").UsedRange.Rows.Count To 2 Step -1
If Sheets("Report Data").Cells(z, iFindCol).Value = "Withdrawal" Then
Sheets("Report Data").Rows(z).Delete
End If
Next z

[Paul_Hossler]

I don't want to use the activesheet code because I'd like to have all codes for this Excel workbook on one single worksheet.

I don't like to use 'ActviveSheet' either, but what do you mean by the second part?

Paul

[doctortt]

For example, I have a worksheet called "VBA Control" and I paste all my codes in this worksheet under visual basic. Going back to the spreadsheet, I create buttons and link each sub to the corresponding button.

[doctortt]

Wops, I pasted my codes to "ThisWorkBook" and it looks like the codes are working now. I'll provide feedbacks again later. Thanks Paul and everyone.

[Paul_Hossler]

... worksheet called "VBA Control and I paste all my codes in this worksheet under visual basic. Going back to the spreadsheet, I create buttons and link each sub to the corresponding button

A worksheet or a module? There is a difference. While you can have code in a worksheet code module, but I usually only ever put code specific to that worksheet there, like button handlers, etc.

The processing code is in a standard module that is called by the button handler, usually passing parameters

Guess I'm being a little dense today

Paul

[doctortt]

Right now, my codes are in "ThisWorkbook" under "Microsoft Excel Objects." Should I put the codes under Modules?

[SamT]

Generally, the Rule of thumb, is:

• That Event code specific to one worksheet goes in that sheets Code.
  ( Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range) )
• That Event code that applies to the specific Workbook and all it's sheets goes in the ThisWorkbook code.
  ( Private Sub object_WorkbookOpen(ByVal Wb As Workbook) )
• Absolute Rule: Event Code that applies across all open workbooks goes in a Class Module.
  ( Dim App As Application
  Private Sub App_WorkbookActivate(ByVal Wb As Workbook))
• That all other code goes in a Module. If more than one Module is used, that they be given descriptive names.

[Paul_Hossler]

SamT's thumb is right on

If you want, this is a very simple example of where I like to put different pieces of code.

WB code in ThisWorkbook, Worksheet code in the specific WS it applies to, and the 'general' purpose code in a Standard module

Paul

[doctortt]

Thank you very much, gentlemen.