VB help
 

My VB coding is not very good....
This code keep erroring out at the first "End If." Any
idea why?
TFTH,
Josh



' Delete the empty rows on "Active Container"

Dim rw As Long, iCol As Integer
For rw = Sheets("Active
Container").UsedRange.Rows.Count To 1 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
Next
End Sub

VB help
 

Put a line

MsgBox rw

before that line. If you get 0 your range is the problem.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Josh Webb" wrote in message
...
My VB coding is not very good....
This code keep erroring out at the first "End If." Any
idea why?
TFTH,
Josh



' Delete the empty rows on "Active Container"

Dim rw As Long, iCol As Integer
For rw = Sheets("Active
Container").UsedRange.Rows.Count To 1 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
Next
End Sub

VB help
 

Bob,
Thanks for the assistance, but it returned 1877 in the
msgbox. I have attached the full VB so you can view where
I maybe lacking.

TFTH again,
Josh

Dim iLastRow As Integer
Dim dLastRow As Integer
Dim i As Integer

If Application.WorksheetFunction.Sum(Sheets("Active
Container").Range("A:A")) = 0 Then
MsgBox "You haven't placed a 1 next to any rows!"
Exit Sub
End If

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Sheets("Active Container").Activate
iLastRow = Cells(Rows.Count, "A").End(xlUp).Row

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

For i = iLastRow To 4 Step -1

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

With Sheets("Active Container").Cells(i, 1)
If .Value = 1 Then
.EntireRow.Cut Sheets("Recent History").Cells
(dLastRow + 1, 1)
End If
End With
Next i

' Delete the empty rows on "Active Container"

Dim rw As Long, iCol As Integer
For rw = Sheets("Active
Container").UsedRange.Rows.Count To 1 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
Next
End Sub
-----Original Message-----
Put a line

MsgBox rw

before that line. If you get 0 your range is the problem.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Josh Webb" wrote
in message
...
My VB coding is not very good....
This code keep erroring out at the first "End If." Any
idea why?
TFTH,
Josh



' Delete the empty rows on "Active Container"

Dim rw As Long, iCol As Integer
For rw = Sheets("Active
Container").UsedRange.Rows.Count To 1 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
Next
End Sub


.

VB help
 

Josh,

I tried it and it worked okay for me with a few small mods to make sure all
objects were properly qualified

Dim iLastRow As Integer
Dim dLastRow As Integer
Dim i As Integer

If Application.WorksheetFunction.Sum(Sheets("Active
Container").Range("A:A")) = 0 Then
MsgBox "You haven't placed a 1 next to any rows!"
Exit Sub
End If

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Sheets("Active Container").Activate
iLastRow = Cells(Rows.Count, "A").End(xlUp).Row

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

For i = iLastRow To 4 Step -1

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

With Sheets("Active Container").Cells(i, 1)
If .Value = 1 Then
.EntireRow.Cut Sheets("Recent History").Cells(dLastRow + 1,
1)
End If
End With
Next i

' Delete the empty rows on "Active Container"

With Sheets("Active Container")
Dim rw As Long, iCol As Integer
For rw = .UsedRange.Rows.Count To 1 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
Next
End With


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Josh Webb" wrote in message
...
Bob,
Thanks for the assistance, but it returned 1877 in the
msgbox. I have attached the full VB so you can view where
I maybe lacking.

TFTH again,
Josh

Dim iLastRow As Integer
Dim dLastRow As Integer
Dim i As Integer

If Application.WorksheetFunction.Sum(Sheets("Active
Container").Range("A:A")) = 0 Then
MsgBox "You haven't placed a 1 next to any rows!"
Exit Sub
End If

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Sheets("Active Container").Activate
iLastRow = Cells(Rows.Count, "A").End(xlUp).Row

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

For i = iLastRow To 4 Step -1

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

With Sheets("Active Container").Cells(i, 1)
If .Value = 1 Then
.EntireRow.Cut Sheets("Recent History").Cells
(dLastRow + 1, 1)
End If
End With
Next i

' Delete the empty rows on "Active Container"

Dim rw As Long, iCol As Integer
For rw = Sheets("Active
Container").UsedRange.Rows.Count To 1 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
Next
End Sub
-----Original Message-----
Put a line

MsgBox rw

before that line. If you get 0 your range is the problem.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Josh Webb" wrote
in message
...
My VB coding is not very good....
This code keep erroring out at the first "End If." Any
idea why?
TFTH,
Josh



' Delete the empty rows on "Active Container"

Dim rw As Long, iCol As Integer
For rw = Sheets("Active
Container").UsedRange.Rows.Count To 1 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
Next
End Sub


.