Hi guys,

I am using the following code to delete rows if the values in column Z = 0

Dim Firstrow As Long
Dim Lastrow As Long
Dim Lrow As Long
Dim CalcMode As Long
Dim ViewMode As Long
With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With
With ActiveSheet
.Select
ViewMode = ActiveWindow.View
ActiveWindow.View = xlNormalView
.DisplayPageBreaks = False
Firstrow = 5
Lastrow = 466
For Lrow = Lastrow To Firstrow Step -1
With .Cells(Lrow, "Z")
If Not IsError(.Value) Then
If .Value = 0 Then .EntireRow.Delete
End If
End With
Next Lrow
End With


I now have a table of values from columns B to W

I was wondering if it is possible to modify the above code or even get help
with a new code that will do the following:

Is there a way to create a row at the end of my table which has the sums of
each column from B to W

Find the last row of values (which has the totals in it)

If any of the values in this row equal zero, then to delete that column

The number of rows will always vary so cannot be set

As always, your help is greatly appreciated

You can detect the last row of data using.... (uses column A to look for
last row, can be changed to any column or you can use index number e.g. for
"A" use 1 etc.)

Lastrow = Cells(Rows.Count,"A").End(xlup).Row

So to put your totals in the next available row, Lastrow + 1

To add the formula use

Cells(Lastrow+1,2).Formula = "=Sum(B5:B" & Lastrow & ")"

You can then copy across the formula into all other columns (use the macro
recorder to get the code)

Finally text each column total for zero

For myCol = 23 to 2 step -1
If Cells(Lastrow+1,myCol).Value = 0 then
Columns(myCol).Delete
End If
Next myCol

--

Regards,
Nigel
(E-Mail Removed)



"Freddy" <(E-Mail Removed)> wrote in message
news:E2DD1BC3-EE11-404D-86D7-(E-Mail Removed)...
> Hi guys,
>
> I am using the following code to delete rows if the values in column Z = 0
>
> Dim Firstrow As Long
> Dim Lastrow As Long
> Dim Lrow As Long
> Dim CalcMode As Long
> Dim ViewMode As Long
> With Application
> CalcMode = .Calculation
> .Calculation = xlCalculationManual
> .ScreenUpdating = False
> End With
> With ActiveSheet
> .Select
> ViewMode = ActiveWindow.View
> ActiveWindow.View = xlNormalView
> .DisplayPageBreaks = False
> Firstrow = 5
> Lastrow = 466
> For Lrow = Lastrow To Firstrow Step -1
> With .Cells(Lrow, "Z")
> If Not IsError(.Value) Then
> If .Value = 0 Then .EntireRow.Delete
> End If
> End With
> Next Lrow
> End With
>
>
> I now have a table of values from columns B to W
>
> I was wondering if it is possible to modify the above code or even get
> help
> with a new code that will do the following:
>
> Is there a way to create a row at the end of my table which has the sums
> of
> each column from B to W
>
> Find the last row of values (which has the totals in it)
>
> If any of the values in this row equal zero, then to delete that column
>
> The number of rows will always vary so cannot be set
>
> As always, your help is greatly appreciated

Nigel,

This works perfectly

You really helped me out on this one.

"Nigel" wrote:

> You can detect the last row of data using.... (uses column A to look for
> last row, can be changed to any column or you can use index number e.g. for
> "A" use 1 etc.)
>
> Lastrow = Cells(Rows.Count,"A").End(xlup).Row
>
> So to put your totals in the next available row, Lastrow + 1
>
> To add the formula use
>
> Cells(Lastrow+1,2).Formula = "=Sum(B5:B" & Lastrow & ")"
>
> You can then copy across the formula into all other columns (use the macro
> recorder to get the code)
>
> Finally text each column total for zero
>
> For myCol = 23 to 2 step -1
> If Cells(Lastrow+1,myCol).Value = 0 then
> Columns(myCol).Delete
> End If
> Next myCol
>
> --
>
> Regards,
> Nigel
> (E-Mail Removed)
>
>
>
> "Freddy" <(E-Mail Removed)> wrote in message
> news:E2DD1BC3-EE11-404D-86D7-(E-Mail Removed)...
> > Hi guys,
> >
> > I am using the following code to delete rows if the values in column Z = 0
> >
> > Dim Firstrow As Long
> > Dim Lastrow As Long
> > Dim Lrow As Long
> > Dim CalcMode As Long
> > Dim ViewMode As Long
> > With Application
> > CalcMode = .Calculation
> > .Calculation = xlCalculationManual
> > .ScreenUpdating = False
> > End With
> > With ActiveSheet
> > .Select
> > ViewMode = ActiveWindow.View
> > ActiveWindow.View = xlNormalView
> > .DisplayPageBreaks = False
> > Firstrow = 5
> > Lastrow = 466
> > For Lrow = Lastrow To Firstrow Step -1
> > With .Cells(Lrow, "Z")
> > If Not IsError(.Value) Then
> > If .Value = 0 Then .EntireRow.Delete
> > End If
> > End With
> > Next Lrow
> > End With
> >
> >
> > I now have a table of values from columns B to W
> >
> > I was wondering if it is possible to modify the above code or even get
> > help
> > with a new code that will do the following:
> >
> > Is there a way to create a row at the end of my table which has the sums
> > of
> > each column from B to W
> >
> > Find the last row of values (which has the totals in it)
> >
> > If any of the values in this row equal zero, then to delete that column
> >
> > The number of rows will always vary so cannot be set
> >
> > As always, your help is greatly appreciated
>