View Single Post
  #3   Report Post  
Old July 7th 06, 05:07 PM posted to microsoft.public.excel.programming
Colin Foster[_5_] Colin Foster[_5_] is offline
external usenet poster
First recorded activity by ExcelBanter: Jun 2006
Posts: 4
Default Using Sheet names & Workbook names in VBA coding

Hi Chip,

Thanks for this.

The code (as it stands at the moment) is...

Public Sub SelectiveDelete()
Dim LRowData As Long, ir As Long

With Sheets("Sheet1")
' detect last row of data in column A
LRowData = .Cells(Rows.Count, "A").End(xlUp).Row

' scan list testing if the value is in range
For ir = LRowData To 1 Step -1
If Trim(.Cells(ir, 1).Value) = "A/C NO" Or _
Trim(.Cells(ir, 1).Value) = "Report Total" Or _
Trim(.Cells(ir, 1).Value) = " Evolut" Or _
Trim(.Cells(ir, 1).Value) = "Evoluti" Or _
Trim(.Cells(ir, 1).Value) = "-------" Or _
InStr(1, Trim(.Cells(ir, 2).Value), "@") 0 Or _
Len(Trim(.Cells(ir, 2).Value)) = 0 _
Then .Rows(ir).Delete Shift:=xlUp
Next ir
End With
End Sub

So where would I put your code? Or, due to the coding that I'm using, do I
need it worded differently?

Thanks, again, for your interest.


"Chip Pearson" wrote in message
Instead of writing code like


you can have to macro refer to whatever sheet is active in Excel.


If you want this to work on all worksheets at one time, use

Dim WS As Worksheet
For Each WS In Worksheets
Next WS

Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC

"Colin Foster" wrote in message
I've got a macro working (thanks to help from this NG) to delete certain
specified rows of data from a spreadsheet. Unfortunately, I have to go
into the VBA code to change th esheet name from "Sheet1" to the actual
name of the sheet. As I want to be able to use this code on more than one
sheet (& possibly across workbooks) is there a simple line of code that I
can put into the VBA to automatically put in th ecorrect sheet name &
file name?

Colin Foster