Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / New Users / April 2007

Tip: Looking for answers? Try searching our database.

My Macro Won't Delete Rows??

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
VexedFist - 15 Apr 2007 05:44 GMT
My Macro does not DELETE the ROWS from the specified Worksheet whene SAVESTR
is NOT Found.

If SAVESTR is found it deletes the other ROWS.  However I need to be left
with a blank sheet if SAVESTR is not found in the specifed column.

Sub DupDigitalSheets()
'
' DupDigitalSheets Macro
' Macro Created On 4/12/2007
'

'
Dim myWorkseheets(11) As String
Dim SAVESTR(11) As String
Dim iCount As Integer
Dim myRange As Range
Dim cell As Range
Dim delRange As Range
Const startRptName = "9006 "
Const stopRptName = " Report.xls"
   SAVESTR(0) = "EXTVCML"
   SAVESTR(1) = "FICTICS"
   SAVESTR(2) = "KYSETDY"
   SAVESTR(3) = "KYSETJR"
   SAVESTR(4) = "OPSLMA"
   SAVESTR(5) = "OPST1"
   SAVESTR(6) = "OPTI"
   SAVESTR(7) = "PHANTOM"
   SAVESTR(8) = "RP4327"
   SAVESTR(9) = "RPHONE"
   SAVESTR(10) = "SADCM"
   myWorkseheets(0) = "Extvcml"
   myWorkseheets(1) = "FICTICS"
   myWorkseheets(2) = "KYSETDY"
   myWorkseheets(3) = "KYSETJR"
   myWorkseheets(4) = "OPSLMA"
   myWorkseheets(5) = "OPST1"
   myWorkseheets(6) = "OptieSets"
   myWorkseheets(7) = "PHANTOM"
   myWorkseheets(8) = "RP4327"
   myWorkseheets(9) = "RPHONE"
   myWorkseheets(10) = "SADCMs"
   Application.ScreenUpdating = False
   Application.DisplayAlerts = False
   For iCount = 0 To 10
   Windows("9006 Digital Line Report.xls").Activate
      Worksheets(myWorkseheets(iCount)).Select
      Worksheets(myWorkseheets(iCount)).Columns("X:X").Select
      On Error Resume Next
      Selection.Find(What:=SAVESTR(iCount), After:=ActiveCell,
LookIn:=xlFormulas, LookAt _
          :=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:= _
          False).Activate
      If Err.Number <> 91 And Err.Number <> 0 Then
          MsgBox "Unresolved Error"
          Exit Sub
      End If
      If ActiveCell.Row > 1 Then
         Set myRange =
Worksheets(myWorkseheets(iCount)).Range("X1").Resize(Range( _
           "X" & Rows.Count).End(xlUp).Row, 1)
         For Each cell In myRange
            If cell.Value <> SAVESTR(iCount) Then
               If delRange Is Nothing Then
                  Set delRange = cell
               Else
                  Set delRange = Union(delRange, cell)
               End If
            End If
         Next cell
       If Not delRange Is Nothing Then delRange.EntireRow.Delete
         Set delRange = Nothing
         Worksheets(myWorkseheets(iCount)).Range("B1").Select
         Worksheets(myWorkseheets(iCount)).Move
         ChDir "C:\Temp Data Files\Reconfigured Data"
         ActiveWorkbook.SaveAs Filename:= _
             startRptName + myWorkseheets(iCount) + stopRptName,
FileFormat:= _
             xlNormal, Password:="", WriteResPassword:="",
ReadOnlyRecommended:=False _
             , CreateBackup:=False
         ActiveWindow.WindowState = xlMinimized
      Else
         Worksheets(myWorkseheets(iCount)).Range("B1").Select
         Worksheets(myWorkseheets(iCount)).Move
         ChDir "C:\Temp Data Files\Reconfigured Data"
         ActiveWorkbook.SaveAs Filename:= _
             startRptName + myWorkseheets(iCount) + stopRptName,
FileFormat:= _
             xlNormal, Password:="", WriteResPassword:="",
ReadOnlyRecommended:=False _
             , CreateBackup:=False
         ActiveWindow.WindowState = xlMinimized
      End If
   Next iCount
   Application.ScreenUpdating = False
End Sub

Any assistance will be appreciated.
Don Guillett - 15 Apr 2007 13:35 GMT
Try this idea.
Sub fori()
myarray1 = Array("a", "b", "c")
myarray2 = Array("x", "y", "z")
For i = 0 To 2
MsgBox myarray1(i)
MsgBox myarray2(i)
Next i
End Sub

Signature

Don Guillett
SalesAid Software
dguillett1@austin.rr.com

> My Macro does not DELETE the ROWS from the specified Worksheet whene
> SAVESTR
[quoted text clipped - 98 lines]
>
> Any assistance will be appreciated.
VexedFist - 16 Apr 2007 15:46 GMT
Don,

 This still goes thru each sheet but does NOT delete the unwanted rows???

> Try this idea.
> Sub fori()
[quoted text clipped - 108 lines]
> >
> > Any assistance will be appreciated.
Don Guillett - 16 Apr 2007 16:14 GMT
Send, to the address below, a small workbook with exactly what you want done
and the code tried so far.

Signature

Don Guillett
SalesAid Software
dguillett1@austin.rr.com

> Don,
>
[quoted text clipped - 113 lines]
>> >
>> > Any assistance will be appreciated.

Rate this thread:






 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.