Delete Columns if value equals zero
 

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

Delete Columns if value equals zero
 

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




"Freddy" wrote in message
...
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

Delete Columns if value equals zero
 

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




"Freddy" wrote in message
...
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