When I run this portion of the VB code I get; Compile
Error: Duplicate declaration in current scope.

'Delete the empty rows on "Data sheet"

Dim rw As Long, iCol As Integer
For rw = Sheets("data sheet").UsedRange.Rows.Count To
3 Step -1
If IsEmpty(Cells(rw, 1)) Then
If Cells(rw, Columns.Count).End(xlToLeft).Column
= 1 Then
Rows(rw).Delete
End If
End If

End Sub


Any assistance with cleaning up this error would be
great. I have also attached all the VB from my Archive
macro for your review.


Sub Archive()
'
' Macro3 Macro
' Macro recorded 11/10/2004 by TTAETSCH
'

'
Dim iLastRow1 As Long
Dim aLastRow As Long
Dim i As Integer
Dim j As Integer
Dim rw As Long, iCol As Integer

'Determine last row in Data Sheet with a "Type of
Discrepancy"

iLastRow1 = Sheets("data sheet").Cells
(Rows.Count, "E").End(xlUp).Row
aLastRow = Sheets("ARCHIEVE").Cells
(Rows.Count, "A").End(xlUp).Row + 1

Application.ScreenUpdating = False

'////////// DATA SHEET ////////////
' Copy and paste each row with a "% of optimum" into the
archive file

For i = iLastRow1 To 3 Step -1
With Sheets("DATA SHEET").Cells(i, "E")
If .Value < "" Then
.EntireRow.Copy
Sheets("ARCHIEVE").Cells
(aLastRow, "A").PasteSpecial _
Paste:=xlPasteValues
End If

End With
aLastRow = aLastRow + 1
Next

' Cut and move rows with 1 in Col. A to "Archieve"

For i = iLastRow To 4 Step -1

dLastRow = Sheets("Archieve").Cells
(Rows.Count, "A").End(xlUp).Row

With Sheets("data sheet").Cells(i, 1)
If .Value = "" Then
.EntireRow.Cut Sheets("Archieve").Cells
(dLastRow + 1, 1)
End If
End With
Next i

'Delete the empty rows on "Data sheet"

Dim rw As Long, iCol As Integer
For rw = Sheets("data sheet").UsedRange.Rows.Count To
3 Step -1
If IsEmpty(Cells(rw, 1)) Then
If Cells(rw, Columns.Count).End(xlToLeft).Column
= 1 Then
Rows(rw).Delete
End If
End If

End Sub

TFTH,
Tom

Hi Tom,

Delete:

Dim rw As Long, iCol As Integer

These variables are previously dimmed at the head of your procedure.

---
Regards,
Norman



"Ton Taetsch" wrote in message
...
When I run this portion of the VB code I get; Compile
Error: Duplicate declaration in current scope.

'Delete the empty rows on "Data sheet"

Dim rw As Long, iCol As Integer
For rw = Sheets("data sheet").UsedRange.Rows.Count To
3 Step -1
If IsEmpty(Cells(rw, 1)) Then
If Cells(rw, Columns.Count).End(xlToLeft).Column
= 1 Then
Rows(rw).Delete
End If
End If

End Sub


Any assistance with cleaning up this error would be
great. I have also attached all the VB from my Archive
macro for your review.


Sub Archive()
'
' Macro3 Macro
' Macro recorded 11/10/2004 by TTAETSCH
'

'
Dim iLastRow1 As Long
Dim aLastRow As Long
Dim i As Integer
Dim j As Integer
Dim rw As Long, iCol As Integer

'Determine last row in Data Sheet with a "Type of
Discrepancy"

iLastRow1 = Sheets("data sheet").Cells
(Rows.Count, "E").End(xlUp).Row
aLastRow = Sheets("ARCHIEVE").Cells
(Rows.Count, "A").End(xlUp).Row + 1

Application.ScreenUpdating = False

'////////// DATA SHEET ////////////
' Copy and paste each row with a "% of optimum" into the
archive file

For i = iLastRow1 To 3 Step -1
With Sheets("DATA SHEET").Cells(i, "E")
If .Value < "" Then
.EntireRow.Copy
Sheets("ARCHIEVE").Cells
(aLastRow, "A").PasteSpecial _
Paste:=xlPasteValues
End If

End With
aLastRow = aLastRow + 1
Next

' Cut and move rows with 1 in Col. A to "Archieve"

For i = iLastRow To 4 Step -1

dLastRow = Sheets("Archieve").Cells
(Rows.Count, "A").End(xlUp).Row

With Sheets("data sheet").Cells(i, 1)
If .Value = "" Then
.EntireRow.Cut Sheets("Archieve").Cells
(dLastRow + 1, 1)
End If
End With
Next i

'Delete the empty rows on "Data sheet"

Dim rw As Long, iCol As Integer
For rw = Sheets("data sheet").UsedRange.Rows.Count To
3 Step -1
If IsEmpty(Cells(rw, 1)) Then
If Cells(rw, Columns.Count).End(xlToLeft).Column
= 1 Then
Rows(rw).Delete
End If
End If

End Sub

TFTH,
Tom