VB Code to automate a sheet clean-up

Hi all

I am fearly useless when it comes to coding in VB as the code below
will prove

Before I give the code I thought I'd share the 'what' I want to
achieve.

I am dumping huge amount of data into excel from an external database
and then want to clean the shpreadsheet removing all the irrelevant
clutter. By that i mean that I want to hide the rows that contain data
where I have 0 as a result.

But i want it as a range result. ie I select B5 to B26, check the total
in =SUM and if it is equal to 0 I want to hide it. Pretty simple uh?


Here is what I tried... and it dosen't work


*Sub SortSheet()*
Dim i As Integer
Dim R As Range
With ActiveSheet
Set R = Range("B6:AB6")
For i = 6 To 6000
If R.Value = 0 Then
R.Select
Selection.EntireRow.Hidden = True
End If
Next R
End With
End Sub


I then tried something else thinking the range was to set...

*Sub SortSheet()*
Dim nRange As Integer
Dim R As Range
With ActiveSheet
For nRange = .Range(.Cells(5, 2), .Cells(5, 28)) To _
.Range(.Cells(6000, 2), .Cells(6000,
28))
If R.Value = 0 Then
R.Select
Selection.EntireRow.Hidden = True
End If
Next nRange
End With
End Sub

That didn't work either...

Please help! I'm lost!!!!!

Many thanks!


---
Message posted from http://www.ExcelForum.com/

Hi
not really sure what values you want to test?
What rows do you want to hide
what are your conditions?

--
Regards
Frank Kabel
Frankfurt, Germany


Hi all

I am fearly useless when it comes to coding in VB as the code below
will prove

Before I give the code I thought I'd share the 'what' I want to
achieve.

I am dumping huge amount of data into excel from an external database
and then want to clean the shpreadsheet removing all the irrelevant
clutter. By that i mean that I want to hide the rows that contain
data
where I have 0 as a result.

But i want it as a range result. ie I select B5 to B26, check the
total in =SUM and if it is equal to 0 I want to hide it. Pretty
simple uh?

Here is what I tried... and it dosen't work


*Sub SortSheet()*
Dim i As Integer
Dim R As Range
With ActiveSheet
Set R = Range("B6:AB6")
For i = 6 To 6000
If R.Value = 0 Then
R.Select
Selection.EntireRow.Hidden = True
End If
Next R
End With
End Sub


I then tried something else thinking the range was to set...

*Sub SortSheet()*
Dim nRange As Integer
Dim R As Range
With ActiveSheet
For nRange = .Range(.Cells(5, 2), .Cells(5, 28)) To _
Range(.Cells(6000, 2), .Cells(6000,
28))
If R.Value = 0 Then
R.Select
Selection.EntireRow.Hidden = True
End If
Next nRange
End With
End Sub

That didn't work either...

Please help! I'm lost!!!!!

Many thanks!


---
Message posted from http://www.ExcelForum.com/

I had a feeling i wasn't being clear sorry!!

Basically I have a set of rows and columns.
In rows i have a list of products, in columns i have months.
In the cells I have sales figure.

What I want to be able to do is take the sum of the range January
December, check if it is equal to 0 and hide it if that returns true.
Then i want to do that row by row...

Hope it's clearer!!


---
Message posted from http://www.ExcelForum.com/

Hi
try the following (column B:M contain your month names, col. A your
products)
Sub hide_rows()
Dim RowNdx As Long
Dim LastRow As Long
application.screenupdating=false
LastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).row
For RowNdx = LastRow To 1 Step -1
if application.sum(range(cells(RowNdx,2),cells(RowNdx ,13)))=0
then
Rows(RowNdx).hidden = True
End If
Next RowNdx
application.screenupdating=true
End Sub

--
Regards
Frank Kabel
Frankfurt, Germany


I had a feeling i wasn't being clear sorry!!

Basically I have a set of rows and columns.
In rows i have a list of products, in columns i have months.
In the cells I have sales figure.

What I want to be able to do is take the sum of the range January
December, check if it is equal to 0 and hide it if that returns true.
Then i want to do that row by row...

Hope it's clearer!!


---
Message posted from http://www.ExcelForum.com/