Hi,

I am wondering if it is possible to do the following.
I have an Excel sheet that is some sort of financial report.
I want to have a function that would go...

If CellFormat = Style1 then "H1"
If CellFormat = Style2 then "H2"
If CellFormat = Style3 then "H3"
If CellFormat = Style4 then "H3"
If CellValue = Total then "TL"
Else the line is "LN"

I'd then extend it to... if LN and (Row value = 0) then hide row.

So it would hide rows if the 'resource code' is 0

The other option is to do it manually but was wondering if it's possible to do it via VBA.

Steven North wrote:

I am wondering if it is possible to do the following.
I have an Excel sheet that is some sort of financial report.
I want to have a function that would go...

If CellFormat = Style1 then "H1"
If CellFormat = Style2 then "H2"
If CellFormat = Style3 then "H3"
If CellFormat = Style4 then "H3"
If CellValue = Total then "TL"
Else the line is "LN"

I'd then extend it to... if LN and (Row value = 0) then hide row.

So it would hide rows if the 'resource code' is 0

The other option is to do it manually but was wondering if it's possible
to do it via VBA.

Something like this, perhaps? This assumes you're using Styles; if you're
using individual elements like bolding, fonts, and/or colors, you'll need to
essentially completely rewrite the entire function...

Function blargh(what As Range)
Select Case what.Style
Case Style1
blargh = "H1"
Case Style2
blargh = "H2"
Case Style3
blargh = "H3"
Case Style4
blargh = "H4"
Case Else
If what.Value = "Total" Then
blargh = "TL"
Else
If Application.WorksheetFunction.Sum(what.EntireRow) = 0 _
Then what.EntireRow.Hidden = True
blargh = "LN"
End If
End Select
End Function

--
I'm glad nobody else thought of that first.

Cheers for that.
I'll give it a go and see what transpires. It looks promising :)

On Tuesday, October 22, 2013 3:44:55 PM UTC+8, Auric__ wrote:
Steven North wrote:



I am wondering if it is possible to do the following.

I have an Excel sheet that is some sort of financial report.

I want to have a function that would go...



If CellFormat = Style1 then "H1"

If CellFormat = Style2 then "H2"

If CellFormat = Style3 then "H3"

If CellFormat = Style4 then "H3"

If CellValue = Total then "TL"

Else the line is "LN"



I'd then extend it to... if LN and (Row value = 0) then hide row.



So it would hide rows if the 'resource code' is 0



The other option is to do it manually but was wondering if it's possible

to do it via VBA.



Something like this, perhaps? This assumes you're using Styles; if you're

using individual elements like bolding, fonts, and/or colors, you'll need to

essentially completely rewrite the entire function...



Function blargh(what As Range)

Select Case what.Style

Case Style1

blargh = "H1"

Case Style2

blargh = "H2"

Case Style3

blargh = "H3"

Case Style4

blargh = "H4"

Case Else

If what.Value = "Total" Then

blargh = "TL"

Else

If Application.WorksheetFunction.Sum(what.EntireRow) = 0 _

Then what.EntireRow.Hidden = True

blargh = "LN"

End If

End Select

End Function



--

I'm glad nobody else thought of that first.