Macro help
 

Hi,

Hope u can help me with the below:

I have a spreadsheet which is exported from a system with a whole lot of 0s
that dont need to be there.

So, the condition is €“ if the contents of C3 down, D3 down, E3 down etc
contains ALL 0 then delete entire column. The spreadsheet spans from about C3
to AH onwards (however the spreadsheet can grow smaller or bigger at any time
so its not good to specify an end column). I need to have this for all the
columns i.e. F3 down, G3 down etc etc.

So, all i want it to do is look from cell 3 downwards in each column and if
the contents of each column it is ALL 0 then to delete the entire column.

I hope this is making sense, what i want isnt so hard but its not easy to
say it!

Thanks for help.

Macro help
 

Hi Gemz,

The following starts from cell C3 (change if necessary e.g. all "C3"s
to "B2" if that's where the data starts), counts the number of columns
to use from C3 to the right (can't handle any blank cells), tests if
the sum of the column in question = 0 and if so adds it into an array
which is deleted once all columns have been tested.

The array is necessary to catch instances where 2 or more sequential
columns sum 0.

Option Explicit
Sub Delete_Zero_Columns()
Dim NumCols As Integer, i As Integer
Dim StartCol As Range, ColArray As Range
Set StartCol = Range("C3")
NumCols = WorksheetFunction.CountA(Range("C3", Range("C3").End
(xlToRight)))

For i = 0 To NumCols - 1
If WorksheetFunction.Sum(StartCol.Offset(0, i).EntireColumn) = 0
Then
If ColArray Is Nothing Then
Set ColArray = StartCol.Offset(0, i).EntireColumn
Else
Set ColArray = Union(ColArray, StartCol.Offset(0,
i).EntireColumn)
End If
End If
Next i
ColArray.Delete
End Sub

HtH,
JF.

On 18 Dec, 10:51, Gemz wrote:
Hi,

Hope u can help me with the below:

I have a spreadsheet which is exported from a system with a whole lot of 0’s
that don’t need to be there.

So, the condition is – if the contents of C3 down, D3 down, E3 down etc
contains ALL 0 then delete entire column. The spreadsheet spans from about C3
to AH onwards (however the spreadsheet can grow smaller or bigger at any time
so its not good to specify an end column). I need to have this for all the
columns i.e. F3 down, G3 down etc etc.

So, all i want it to do is look from cell 3 downwards in each column and if
the contents of each column it is ALL 0 then to delete the entire column.

I hope this is making sense, what i want isn’t so hard but its not easy to
say it!

Thanks for help.

Macro help
 

Hi,

Thanks for your reply. i put the code in and it already it highlighted 3
rows in red and i didnt know how to fix:

1 - NumCols = WorksheetFunction.CountA(Range("C3", Range("C3").End
(xlToRight)))

2 - If WorksheetFunction.Sum(StartCol.Offset(0, i).EntireColumn) = 0
Then

3 - Set ColArray = Union(ColArray, StartCol.Offset(0,
i).EntireColumn)


thanks.



"Joshua Fandango" wrote:

Hi Gemz,

The following starts from cell C3 (change if necessary e.g. all "C3"s
to "B2" if that's where the data starts), counts the number of columns
to use from C3 to the right (can't handle any blank cells), tests if
the sum of the column in question = 0 and if so adds it into an array
which is deleted once all columns have been tested.

The array is necessary to catch instances where 2 or more sequential
columns sum 0.

Option Explicit
Sub Delete_Zero_Columns()
Dim NumCols As Integer, i As Integer
Dim StartCol As Range, ColArray As Range
Set StartCol = Range("C3")
NumCols = WorksheetFunction.CountA(Range("C3", Range("C3").End
(xlToRight)))

For i = 0 To NumCols - 1
If WorksheetFunction.Sum(StartCol.Offset(0, i).EntireColumn) = 0
Then
If ColArray Is Nothing Then
Set ColArray = StartCol.Offset(0, i).EntireColumn
Else
Set ColArray = Union(ColArray, StartCol.Offset(0,
i).EntireColumn)
End If
End If
Next i
ColArray.Delete
End Sub

HtH,
JF.

On 18 Dec, 10:51, Gemz wrote:
Hi,

Hope u can help me with the below:

I have a spreadsheet which is exported from a system with a whole lot of 0s
that dont need to be there.

So, the condition is €“ if the contents of C3 down, D3 down, E3 down etc
contains ALL 0 then delete entire column. The spreadsheet spans from about C3
to AH onwards (however the spreadsheet can grow smaller or bigger at any time
so its not good to specify an end column). I need to have this for all the
columns i.e. F3 down, G3 down etc etc.

So, all i want it to do is look from cell 3 downwards in each column and if
the contents of each column it is ALL 0 then to delete the entire column.

I hope this is making sense, what i want isnt so hard but its not easy to
say it!

Thanks for help.

Macro help
 

Hi

The problem is word wrapping in your news reader.

Place the cursor at the end of the first red line and hit Delete to
delete the line feed. Move on to the next line.

Hopes it helps

---
Per

On 18 Dec., 14:33, Gemz wrote:
Hi,

Thanks for your reply. i put the code in and it already it highlighted 3
rows in red and i didnt know how to fix:

*1 - NumCols = WorksheetFunction.CountA(Range("C3", Range("C3").End
(xlToRight)))

2 - If WorksheetFunction.Sum(StartCol.Offset(0, i).EntireColumn) = 0
Then

3 - Set ColArray = Union(ColArray, StartCol.Offset(0,
i).EntireColumn)

thanks.



"Joshua Fandango" wrote:
Hi Gemz,

The following starts from cell C3 (change if necessary e.g. all "C3"s
to "B2" if that's where the data starts), counts the number of columns
to use from C3 to the right (can't handle any blank cells), tests if
the sum of the column in question = 0 and if so adds it into an array
which is deleted once all columns have been tested.

The array is necessary to catch instances where 2 or more sequential
columns sum 0.

Option Explicit
Sub Delete_Zero_Columns()
Dim NumCols As Integer, i As Integer
Dim StartCol As Range, ColArray As Range
* Set StartCol = Range("C3")
* NumCols = WorksheetFunction.CountA(Range("C3", Range("C3").End
(xlToRight)))

* For i = 0 To NumCols - 1
* * If WorksheetFunction.Sum(StartCol.Offset(0, i).EntireColumn) = 0
Then
* * * If ColArray Is Nothing Then
* * * * Set ColArray = StartCol.Offset(0, i).EntireColumn
* * * Else
* * * * Set ColArray = Union(ColArray, StartCol.Offset(0,
i).EntireColumn)
* * * End If
* * End If
* Next i
* ColArray.Delete
End Sub

HtH,
JF.

On 18 Dec, 10:51, Gemz wrote:
Hi,

Hope u can help me with the below:

I have a spreadsheet which is exported from a system with a whole lot of 0’s
that don’t need to be there.

So, the condition is – if the contents of C3 down, D3 down, E3 down etc
contains ALL 0 then delete entire column. The spreadsheet spans from about C3
to AH onwards (however the spreadsheet can grow smaller or bigger at any time
so its not good to specify an end column). I need to have this for all the
columns i.e. F3 down, G3 down etc etc.

So, all i want it to do is look from cell 3 downwards in each column and if
the contents of each column it is ALL 0 then to delete the entire column.

I hope this is making sense, what i want isn’t so hard but its not easy to
say it!

Thanks for help.- Skjul tekst i anførselstegn -

- Vis tekst i anførselstegn -

Macro help
 

Hi,

Thanks for response. I did what you said and tried running the macro and now
i get the below error:

Runtime Error 91
Object Variable or with block variable not set.

I dont know what this means, any help please?

thanks.

"Per Jessen" wrote:

Hi

The problem is word wrapping in your news reader.

Place the cursor at the end of the first red line and hit Delete to
delete the line feed. Move on to the next line.

Hopes it helps

---
Per

On 18 Dec., 14:33, Gemz wrote:
Hi,

Thanks for your reply. i put the code in and it already it highlighted 3
rows in red and i didnt know how to fix:

1 - NumCols = WorksheetFunction.CountA(Range("C3", Range("C3").End
(xlToRight)))

2 - If WorksheetFunction.Sum(StartCol.Offset(0, i).EntireColumn) = 0
Then

3 - Set ColArray = Union(ColArray, StartCol.Offset(0,
i).EntireColumn)

thanks.



"Joshua Fandango" wrote:
Hi Gemz,

The following starts from cell C3 (change if necessary e.g. all "C3"s
to "B2" if that's where the data starts), counts the number of columns
to use from C3 to the right (can't handle any blank cells), tests if
the sum of the column in question = 0 and if so adds it into an array
which is deleted once all columns have been tested.

The array is necessary to catch instances where 2 or more sequential
columns sum 0.

Option Explicit
Sub Delete_Zero_Columns()
Dim NumCols As Integer, i As Integer
Dim StartCol As Range, ColArray As Range
Set StartCol = Range("C3")
NumCols = WorksheetFunction.CountA(Range("C3", Range("C3").End
(xlToRight)))

For i = 0 To NumCols - 1
If WorksheetFunction.Sum(StartCol.Offset(0, i).EntireColumn) = 0
Then
If ColArray Is Nothing Then
Set ColArray = StartCol.Offset(0, i).EntireColumn
Else
Set ColArray = Union(ColArray, StartCol.Offset(0,
i).EntireColumn)
End If
End If
Next i
ColArray.Delete
End Sub

HtH,
JF.

On 18 Dec, 10:51, Gemz wrote:
Hi,

Hope u can help me with the below:

I have a spreadsheet which is exported from a system with a whole lot of 0s
that dont need to be there.

So, the condition is €“ if the contents of C3 down, D3 down, E3 down etc
contains ALL 0 then delete entire column. The spreadsheet spans from about C3
to AH onwards (however the spreadsheet can grow smaller or bigger at any time
so its not good to specify an end column). I need to have this for all the
columns i.e. F3 down, G3 down etc etc.

So, all i want it to do is look from cell 3 downwards in each column and if
the contents of each column it is ALL 0 then to delete the entire column.

I hope this is making sense, what i want isnt so hard but its not easy to
say it!

Thanks for help.- Skjul tekst i anførselstegn -

- Vis tekst i anførselstegn -

Macro help
 

Hi again

I',m not sure, but it's the line "ColArray,Delete" which cause the error,
then it's because no columns to delete is found.

Try this:

Sub Delete_Zero_Columns()
Dim msg As String
Dim NumCols As Integer, i As Integer
Dim StartCol As Range, ColArray As Range
Set StartCol = Range("C3")
NumCols = WorksheetFunction.CountA(Range("C3",
Range("C3").End(xlToRight)))


For i = 0 To NumCols - 1
If WorksheetFunction.Sum(StartCol.Offset(0, i).EntireColumn) = 0 Then
If ColArray Is Nothing Then
Set ColArray = StartCol.Offset(0, i).EntireColumn
Else
Set ColArray = Union(ColArray, StartCol.Offset(0, i).EntireColumn)
End If
End If
Next i
If ColArray Is Nothing Then
msg = MsgBox("No columns to delete")
Else
ColArray.Delete
End If
End Sub

---
Per

"Gemz" skrev i meddelelsen
...
Hi,

Thanks for response. I did what you said and tried running the macro and
now
i get the below error:

Runtime Error 91
Object Variable or with block variable not set.

I dont know what this means, any help please?

thanks.

"Per Jessen" wrote:

Hi

The problem is word wrapping in your news reader.

Place the cursor at the end of the first red line and hit Delete to
delete the line feed. Move on to the next line.

Hopes it helps

---
Per

On 18 Dec., 14:33, Gemz wrote:
Hi,

Thanks for your reply. i put the code in and it already it highlighted
3
rows in red and i didnt know how to fix:

1 - NumCols = WorksheetFunction.CountA(Range("C3", Range("C3").End
(xlToRight)))

2 - If WorksheetFunction.Sum(StartCol.Offset(0, i).EntireColumn) = 0
Then

3 - Set ColArray = Union(ColArray, StartCol.Offset(0,
i).EntireColumn)

thanks.



"Joshua Fandango" wrote:
Hi Gemz,

The following starts from cell C3 (change if necessary e.g. all "C3"s
to "B2" if that's where the data starts), counts the number of
columns
to use from C3 to the right (can't handle any blank cells), tests if
the sum of the column in question = 0 and if so adds it into an array
which is deleted once all columns have been tested.

The array is necessary to catch instances where 2 or more sequential
columns sum 0.

Option Explicit
Sub Delete_Zero_Columns()
Dim NumCols As Integer, i As Integer
Dim StartCol As Range, ColArray As Range
Set StartCol = Range("C3")
NumCols = WorksheetFunction.CountA(Range("C3", Range("C3").End
(xlToRight)))

For i = 0 To NumCols - 1
If WorksheetFunction.Sum(StartCol.Offset(0, i).EntireColumn) = 0
Then
If ColArray Is Nothing Then
Set ColArray = StartCol.Offset(0, i).EntireColumn
Else
Set ColArray = Union(ColArray, StartCol.Offset(0,
i).EntireColumn)
End If
End If
Next i
ColArray.Delete
End Sub

HtH,
JF.

On 18 Dec, 10:51, Gemz wrote:
Hi,

Hope u can help me with the below:

I have a spreadsheet which is exported from a system with a whole
lot of 0s
that dont need to be there.

So, the condition is €“ if the contents of C3 down, D3 down, E3 down
etc
contains ALL 0 then delete entire column. The spreadsheet spans
from about C3
to AH onwards (however the spreadsheet can grow smaller or bigger
at any time
so its not good to specify an end column). I need to have this for
all the
columns i.e. F3 down, G3 down etc etc.

So, all i want it to do is look from cell 3 downwards in each
column and if
the contents of each column it is ALL 0 then to delete the entire
column.

I hope this is making sense, what i want isnt so hard but its not
easy to
say it!

Thanks for help.- Skjul tekst i anførselstegn -

- Vis tekst i anførselstegn -

Macro help
 

Hi,

Thanks. this time the macro ran without any errors but.. it didnt do its
job. it came up with the message 'no columns to delete' when there are a few
columns that have 0's in alll the cells from C3 downwards.

Maybe i didnt make the requirement clear.. if all cells C3 downward, D3
downwards ,E3 downwards etc etc and for the rest of the sheet are 0 (all the
cells from row 3 downwards have numbers, only if the column that has each
cell containing 0 value should it delete the column.

I hope im making sense!

thank you

"Per Jessen" wrote:

Hi again

I',m not sure, but it's the line "ColArray,Delete" which cause the error,
then it's because no columns to delete is found.

Try this:

Sub Delete_Zero_Columns()
Dim msg As String
Dim NumCols As Integer, i As Integer
Dim StartCol As Range, ColArray As Range
Set StartCol = Range("C3")
NumCols = WorksheetFunction.CountA(Range("C3",
Range("C3").End(xlToRight)))


For i = 0 To NumCols - 1
If WorksheetFunction.Sum(StartCol.Offset(0, i).EntireColumn) = 0 Then
If ColArray Is Nothing Then
Set ColArray = StartCol.Offset(0, i).EntireColumn
Else
Set ColArray = Union(ColArray, StartCol.Offset(0, i).EntireColumn)
End If
End If
Next i
If ColArray Is Nothing Then
msg = MsgBox("No columns to delete")
Else
ColArray.Delete
End If
End Sub

---
Per

"Gemz" skrev i meddelelsen
...
Hi,

Thanks for response. I did what you said and tried running the macro and
now
i get the below error:

Runtime Error 91
Object Variable or with block variable not set.

I dont know what this means, any help please?

thanks.

"Per Jessen" wrote:

Hi

The problem is word wrapping in your news reader.

Place the cursor at the end of the first red line and hit Delete to
delete the line feed. Move on to the next line.

Hopes it helps

---
Per

On 18 Dec., 14:33, Gemz wrote:
Hi,

Thanks for your reply. i put the code in and it already it highlighted
3
rows in red and i didnt know how to fix:

1 - NumCols = WorksheetFunction.CountA(Range("C3", Range("C3").End
(xlToRight)))

2 - If WorksheetFunction.Sum(StartCol.Offset(0, i).EntireColumn) = 0
Then

3 - Set ColArray = Union(ColArray, StartCol.Offset(0,
i).EntireColumn)

thanks.



"Joshua Fandango" wrote:
Hi Gemz,

The following starts from cell C3 (change if necessary e.g. all "C3"s
to "B2" if that's where the data starts), counts the number of
columns
to use from C3 to the right (can't handle any blank cells), tests if
the sum of the column in question = 0 and if so adds it into an array
which is deleted once all columns have been tested.

The array is necessary to catch instances where 2 or more sequential
columns sum 0.

Option Explicit
Sub Delete_Zero_Columns()
Dim NumCols As Integer, i As Integer
Dim StartCol As Range, ColArray As Range
Set StartCol = Range("C3")
NumCols = WorksheetFunction.CountA(Range("C3", Range("C3").End
(xlToRight)))

For i = 0 To NumCols - 1
If WorksheetFunction.Sum(StartCol.Offset(0, i).EntireColumn) = 0
Then
If ColArray Is Nothing Then
Set ColArray = StartCol.Offset(0, i).EntireColumn
Else
Set ColArray = Union(ColArray, StartCol.Offset(0,
i).EntireColumn)
End If
End If
Next i
ColArray.Delete
End Sub

HtH,
JF.

On 18 Dec, 10:51, Gemz wrote:
Hi,

Hope u can help me with the below:

I have a spreadsheet which is exported from a system with a whole
lot of 0s
that dont need to be there.

So, the condition is €“ if the contents of C3 down, D3 down, E3 down
etc
contains ALL 0 then delete entire column. The spreadsheet spans
from about C3
to AH onwards (however the spreadsheet can grow smaller or bigger
at any time
so its not good to specify an end column). I need to have this for
all the
columns i.e. F3 down, G3 down etc etc.

So, all i want it to do is look from cell 3 downwards in each
column and if
the contents of each column it is ALL 0 then to delete the entire
column.

I hope this is making sense, what i want isnt so hard but its not
easy to
say it!

Thanks for help.- Skjul tekst i anførselstegn -

- Vis tekst i anførselstegn -

Macro help
 

Hi

I tested the macro on some numbers entered directly, and it's working
as you desire.

If the values in the columns are calculated, they may be "false" zero
values. As excel calculates it can result in some very small decimal
numbers. Try to myltiply cells with zeros by 1000000, and see if the
result is still 0, or enter zero values directly in a sample sheet to
test the macro.

Regards,
Per

On 19 Dec., 12:48, Gemz wrote:
Hi,

Thanks. this time the macro ran without any errors but.. it didnt do its
job. it came up with the message 'no columns to delete' when there are a few
columns that have 0's in alll the cells from C3 downwards.

Maybe i didnt make the requirement clear.. if all cells C3 downward, D3
downwards ,E3 downwards etc etc and for the rest of the sheet are 0 (all the
cells from row 3 downwards have numbers, only if the column that has each
cell containing 0 value should it delete the column.

I hope im making sense!

thank you



"Per Jessen" wrote:
Hi again

I',m not sure, but it's the line "ColArray,Delete" which cause the error,
then it's because no columns to delete is found.

Try this:

Sub Delete_Zero_Columns()
Dim msg As String
Dim NumCols As Integer, i As Integer
Dim StartCol As Range, ColArray As Range
* Set StartCol = Range("C3")
* NumCols = WorksheetFunction.CountA(Range("C3",
Range("C3").End(xlToRight)))

* For i = 0 To NumCols - 1
* * If WorksheetFunction.Sum(StartCol.Offset(0, i).EntireColumn) = 0 Then
* * * If ColArray Is Nothing Then
* * * * Set ColArray = StartCol.Offset(0, i).EntireColumn
* * * Else
* * * * Set ColArray = Union(ColArray, StartCol.Offset(0, i).EntireColumn)
* * * End If
* * End If
* Next i
* If ColArray Is Nothing Then
* * msg = MsgBox("No columns to delete")
* Else
* * ColArray.Delete
* End If
End Sub

---
Per

"Gemz" skrev i meddelelsen
...
Hi,

Thanks for response. I did what you said and tried running the macro and
now
i get the below error:

Runtime Error 91
Object Variable or with block variable not set.

I dont know what this means, any help please?

thanks.

"Per Jessen" wrote:

Hi

The problem is word wrapping in your news reader.

Place the cursor at the end of the first red line and hit Delete to
delete the line feed. Move on to the next line.

Hopes it helps

---
Per

On 18 Dec., 14:33, Gemz wrote:
Hi,

Thanks for your reply. i put the code in and it already it highlighted
3
rows in red and i didnt know how to fix:

*1 - NumCols = WorksheetFunction.CountA(Range("C3", Range("C3").End
(xlToRight)))

2 - If WorksheetFunction.Sum(StartCol.Offset(0, i).EntireColumn) = 0
Then

3 - Set ColArray = Union(ColArray, StartCol.Offset(0,
i).EntireColumn)

thanks.

"Joshua Fandango" wrote:
Hi Gemz,

The following starts from cell C3 (change if necessary e.g. all "C3"s
to "B2" if that's where the data starts), counts the number of
columns
to use from C3 to the right (can't handle any blank cells), tests if
the sum of the column in question = 0 and if so adds it into an array
which is deleted once all columns have been tested.

The array is necessary to catch instances where 2 or more sequential
columns sum 0.

Option Explicit
Sub Delete_Zero_Columns()
Dim NumCols As Integer, i As Integer
Dim StartCol As Range, ColArray As Range
* Set StartCol = Range("C3")
* NumCols = WorksheetFunction.CountA(Range("C3", Range("C3")..End
(xlToRight)))

* For i = 0 To NumCols - 1
* * If WorksheetFunction.Sum(StartCol.Offset(0, i).EntireColumn) = 0
Then
* * * If ColArray Is Nothing Then
* * * * Set ColArray = StartCol.Offset(0, i).EntireColumn
* * * Else
* * * * Set ColArray = Union(ColArray, StartCol.Offset(0,
i).EntireColumn)
* * * End If
* * End If
* Next i
* ColArray.Delete
End Sub

HtH,
JF.

On 18 Dec, 10:51, Gemz wrote:
Hi,

Hope u can help me with the below:

I have a spreadsheet which is exported from a system with a whole
lot of 0’s
that don’t need to be there.

So, the condition is – if the contents of C3 down, D3 down, E3 down
etc
contains ALL 0 then delete entire column. The spreadsheet spans
from about C3
to AH onwards (however the spreadsheet can grow smaller or bigger
at any time
so its not good to specify an end column). I need to have this for
all the
columns i.e. F3 down, G3 down etc etc.

So, all i want it to do is look from cell 3 downwards in each
column and if
the contents of each column it is ALL 0 then to delete the entire
column.

I hope this is making sense, what i want isn’t so hard but its not
easy to
say it!

Thanks for help.- Skjul tekst i anførselstegn -

- Vis tekst i anførselstegn -- Skjul tekst i anførselstegn -

- Vis tekst i anførselstegn -

Macro help
 

Hi,

The spreadsheet i am working with is actually a download from another system
so maybe you're right - the 0's can be 'false'.

I entered some 0 values in cells manually and tried the macro but it still
doesnt work! i dont know what i am doing wrong.. is there any way i can email
u a sample spreadsheet so u can see why this is happening?

Really appreciate all your help

"Per Jessen" wrote:

Hi

I tested the macro on some numbers entered directly, and it's working
as you desire.

If the values in the columns are calculated, they may be "false" zero
values. As excel calculates it can result in some very small decimal
numbers. Try to myltiply cells with zeros by 1000000, and see if the
result is still 0, or enter zero values directly in a sample sheet to
test the macro.

Regards,
Per

On 19 Dec., 12:48, Gemz wrote:
Hi,

Thanks. this time the macro ran without any errors but.. it didnt do its
job. it came up with the message 'no columns to delete' when there are a few
columns that have 0's in alll the cells from C3 downwards.

Maybe i didnt make the requirement clear.. if all cells C3 downward, D3
downwards ,E3 downwards etc etc and for the rest of the sheet are 0 (all the
cells from row 3 downwards have numbers, only if the column that has each
cell containing 0 value should it delete the column.

I hope im making sense!

thank you



"Per Jessen" wrote:
Hi again

I',m not sure, but it's the line "ColArray,Delete" which cause the error,
then it's because no columns to delete is found.

Try this:

Sub Delete_Zero_Columns()
Dim msg As String
Dim NumCols As Integer, i As Integer
Dim StartCol As Range, ColArray As Range
Set StartCol = Range("C3")
NumCols = WorksheetFunction.CountA(Range("C3",
Range("C3").End(xlToRight)))

For i = 0 To NumCols - 1
If WorksheetFunction.Sum(StartCol.Offset(0, i).EntireColumn) = 0 Then
If ColArray Is Nothing Then
Set ColArray = StartCol.Offset(0, i).EntireColumn
Else
Set ColArray = Union(ColArray, StartCol.Offset(0, i).EntireColumn)
End If
End If
Next i
If ColArray Is Nothing Then
msg = MsgBox("No columns to delete")
Else
ColArray.Delete
End If
End Sub

---
Per

"Gemz" skrev i meddelelsen
...
Hi,

Thanks for response. I did what you said and tried running the macro and
now
i get the below error:

Runtime Error 91
Object Variable or with block variable not set.

I dont know what this means, any help please?

thanks.

"Per Jessen" wrote:

Hi

The problem is word wrapping in your news reader.

Place the cursor at the end of the first red line and hit Delete to
delete the line feed. Move on to the next line.

Hopes it helps

---
Per

On 18 Dec., 14:33, Gemz wrote:
Hi,

Thanks for your reply. i put the code in and it already it highlighted
3
rows in red and i didnt know how to fix:

1 - NumCols = WorksheetFunction.CountA(Range("C3", Range("C3").End
(xlToRight)))

2 - If WorksheetFunction.Sum(StartCol.Offset(0, i).EntireColumn) = 0
Then

3 - Set ColArray = Union(ColArray, StartCol.Offset(0,
i).EntireColumn)

thanks.

"Joshua Fandango" wrote:
Hi Gemz,

The following starts from cell C3 (change if necessary e.g. all "C3"s
to "B2" if that's where the data starts), counts the number of
columns
to use from C3 to the right (can't handle any blank cells), tests if
the sum of the column in question = 0 and if so adds it into an array
which is deleted once all columns have been tested.

The array is necessary to catch instances where 2 or more sequential
columns sum 0.

Option Explicit
Sub Delete_Zero_Columns()
Dim NumCols As Integer, i As Integer
Dim StartCol As Range, ColArray As Range
Set StartCol = Range("C3")
NumCols = WorksheetFunction.CountA(Range("C3", Range("C3")..End
(xlToRight)))

For i = 0 To NumCols - 1
If WorksheetFunction.Sum(StartCol.Offset(0, i).EntireColumn) = 0
Then
If ColArray Is Nothing Then
Set ColArray = StartCol.Offset(0, i).EntireColumn
Else
Set ColArray = Union(ColArray, StartCol.Offset(0,
i).EntireColumn)
End If
End If
Next i
ColArray.Delete
End Sub

HtH,
JF.

On 18 Dec, 10:51, Gemz wrote:
Hi,

Hope u can help me with the below:

I have a spreadsheet which is exported from a system with a whole
lot of 0s
that dont need to be there.

So, the condition is €“ if the contents of C3 down, D3 down, E3 down
etc
contains ALL 0 then delete entire column. The spreadsheet spans
from about C3
to AH onwards (however the spreadsheet can grow smaller or bigger
at any time
so its not good to specify an end column). I need to have this for
all the
columns i.e. F3 down, G3 down etc etc.

So, all i want it to do is look from cell 3 downwards in each
column and if
the contents of each column it is ALL 0 then to delete the entire
column.

I hope this is making sense, what i want isnt so hard but its not
easy to
say it!

Thanks for help.- Skjul tekst i anførselstegn -

- Vis tekst i anførselstegn -- Skjul tekst i anførselstegn -

- Vis tekst i anførselstegn -

Macro help
 

Hi

Thanks for your reply. Mail me a sample sheet, and I will look at it.

Regards,
Per

"Gemz" skrev i meddelelsen
...
Hi,

The spreadsheet i am working with is actually a download from another
system
so maybe you're right - the 0's can be 'false'.

I entered some 0 values in cells manually and tried the macro but it still
doesnt work! i dont know what i am doing wrong.. is there any way i can
email
u a sample spreadsheet so u can see why this is happening?

Really appreciate all your help

"Per Jessen" wrote:

Hi

I tested the macro on some numbers entered directly, and it's working
as you desire.

If the values in the columns are calculated, they may be "false" zero
values. As excel calculates it can result in some very small decimal
numbers. Try to myltiply cells with zeros by 1000000, and see if the
result is still 0, or enter zero values directly in a sample sheet to
test the macro.

Regards,
Per

On 19 Dec., 12:48, Gemz wrote:
Hi,

Thanks. this time the macro ran without any errors but.. it didnt do
its
job. it came up with the message 'no columns to delete' when there are
a few
columns that have 0's in alll the cells from C3 downwards.

Maybe i didnt make the requirement clear.. if all cells C3 downward, D3
downwards ,E3 downwards etc etc and for the rest of the sheet are 0
(all the
cells from row 3 downwards have numbers, only if the column that has
each
cell containing 0 value should it delete the column.

I hope im making sense!

thank you



"Per Jessen" wrote:
Hi again

I',m not sure, but it's the line "ColArray,Delete" which cause the
error,
then it's because no columns to delete is found.

Try this:

Sub Delete_Zero_Columns()
Dim msg As String
Dim NumCols As Integer, i As Integer
Dim StartCol As Range, ColArray As Range
Set StartCol = Range("C3")
NumCols = WorksheetFunction.CountA(Range("C3",
Range("C3").End(xlToRight)))

For i = 0 To NumCols - 1
If WorksheetFunction.Sum(StartCol.Offset(0, i).EntireColumn) = 0
Then
If ColArray Is Nothing Then
Set ColArray = StartCol.Offset(0, i).EntireColumn
Else
Set ColArray = Union(ColArray, StartCol.Offset(0,
i).EntireColumn)
End If
End If
Next i
If ColArray Is Nothing Then
msg = MsgBox("No columns to delete")
Else
ColArray.Delete
End If
End Sub

---
Per

"Gemz" skrev i meddelelsen
...
Hi,

Thanks for response. I did what you said and tried running the
macro and
now
i get the below error:

Runtime Error 91
Object Variable or with block variable not set.

I dont know what this means, any help please?

thanks.

"Per Jessen" wrote:

Hi

The problem is word wrapping in your news reader.

Place the cursor at the end of the first red line and hit Delete
to
delete the line feed. Move on to the next line.

Hopes it helps

---
Per

On 18 Dec., 14:33, Gemz wrote:
Hi,

Thanks for your reply. i put the code in and it already it
highlighted
3
rows in red and i didnt know how to fix:

1 - NumCols = WorksheetFunction.CountA(Range("C3",
Range("C3").End
(xlToRight)))

2 - If WorksheetFunction.Sum(StartCol.Offset(0, i).EntireColumn)
= 0
Then

3 - Set ColArray = Union(ColArray, StartCol.Offset(0,
i).EntireColumn)

thanks.

"Joshua Fandango" wrote:
Hi Gemz,

The following starts from cell C3 (change if necessary e.g.
all "C3"s
to "B2" if that's where the data starts), counts the number of
columns
to use from C3 to the right (can't handle any blank cells),
tests if
the sum of the column in question = 0 and if so adds it into
an array
which is deleted once all columns have been tested.

The array is necessary to catch instances where 2 or more
sequential
columns sum 0.

Option Explicit
Sub Delete_Zero_Columns()
Dim NumCols As Integer, i As Integer
Dim StartCol As Range, ColArray As Range
Set StartCol = Range("C3")
NumCols = WorksheetFunction.CountA(Range("C3",
Range("C3")..End
(xlToRight)))

For i = 0 To NumCols - 1
If WorksheetFunction.Sum(StartCol.Offset(0,
i).EntireColumn) = 0
Then
If ColArray Is Nothing Then
Set ColArray = StartCol.Offset(0, i).EntireColumn
Else
Set ColArray = Union(ColArray, StartCol.Offset(0,
i).EntireColumn)
End If
End If
Next i
ColArray.Delete
End Sub

HtH,
JF.

On 18 Dec, 10:51, Gemz wrote:
Hi,

Hope u can help me with the below:

I have a spreadsheet which is exported from a system with a
whole
lot of 0s
that dont need to be there.

So, the condition is €“ if the contents of C3 down, D3 down,
E3 down
etc
contains ALL 0 then delete entire column. The spreadsheet
spans
from about C3
to AH onwards (however the spreadsheet can grow smaller or
bigger
at any time
so its not good to specify an end column). I need to have
this for
all the
columns i.e. F3 down, G3 down etc etc.

So, all i want it to do is look from cell 3 downwards in
each
column and if
the contents of each column it is ALL 0 then to delete the
entire
column.

I hope this is making sense, what i want isnt so hard but
its not
easy to
say it!

Thanks for help.- Skjul tekst i anførselstegn -

- Vis tekst i anførselstegn -- Skjul tekst i anførselstegn -

- Vis tekst i anførselstegn -