I am designing a price list, which different branches will use different
options on, ie, some will offer products A and C, but not product B; others
will offer all 3. I am struggling to work out how to design this as a macro.
The idea would be that if they "tick" A, B and C, then they would then be
promted to put in some prices, which would in turn generate further
discounted price lists. Any ideas? thanks, Kelly

I have used this one before, it may work for you also.

Option Explicit

Private Sub Worksheet_Activate()

Dim HiddenRow&, RowRange As Range

'< Set the column that contains data
Const DataCol As String = "A"

'*****************************

ActiveWindow.DisplayZeros = False
Application.ScreenUpdating = False

For HiddenRow = 1 To ActiveSheet.UsedRange.Rows.Count

'Take the Length of the value in column A of each row
If Len(Range(DataCol & HiddenRow).Value) < 0 Then
'there's something in this row - don't hide
Rows(HiddenRow).EntireRow.Hidden = False
Else
'there's nothing in this row yet - hide it
Rows(HiddenRow).EntireRow.Hidden = True
End If

Next HiddenRow

Application.ScreenUpdating = True

End Sub


"plumfin" wrote:

I am designing a price list, which different branches will use different
options on, ie, some will offer products A and C, but not product B; others
will offer all 3. I am struggling to work out how to design this as a macro.
The idea would be that if they "tick" A, B and C, then they would then be
promted to put in some prices, which would in turn generate further
discounted price lists. Any ideas? thanks, Kelly

Thanks Cerberus, I must be having a dim day - what is Option Explicit? Also,
should i copy this text exactly and paste it into my spreadsheet? thanks,
Kelly

"Cerberus" wrote:

I have used this one before, it may work for you also.

Option Explicit

Private Sub Worksheet_Activate()

Dim HiddenRow&, RowRange As Range

'< Set the column that contains data
Const DataCol As String = "A"

'*****************************

ActiveWindow.DisplayZeros = False
Application.ScreenUpdating = False

For HiddenRow = 1 To ActiveSheet.UsedRange.Rows.Count

'Take the Length of the value in column A of each row
If Len(Range(DataCol & HiddenRow).Value) < 0 Then
'there's something in this row - don't hide
Rows(HiddenRow).EntireRow.Hidden = False
Else
'there's nothing in this row yet - hide it
Rows(HiddenRow).EntireRow.Hidden = True
End If

Next HiddenRow

Application.ScreenUpdating = True

End Sub


"plumfin" wrote:

I am designing a price list, which different branches will use different
options on, ie, some will offer products A and C, but not product B; others
will offer all 3. I am struggling to work out how to design this as a macro.
The idea would be that if they "tick" A, B and C, then they would then be
promted to put in some prices, which would in turn generate further
discounted price lists. Any ideas? thanks, Kelly

Sorry, you don't need the Option Explicit part. You would want to go in to
the VBE (Alt+F11) and click on the sheet you want to show only the cells that
have info. You will find the sheets in the Microsoft Excel Objects folder in
the VBAProject area on the left hand side of your screen. Once you "click"
on the sheet, paste the code in the General field. With this code, once the
sheet has been opened all your blank cells will be hidden.

"plumfin" wrote:

Thanks Cerberus, I must be having a dim day - what is Option Explicit? Also,
should i copy this text exactly and paste it into my spreadsheet? thanks,
Kelly

"Cerberus" wrote:

I have used this one before, it may work for you also.

Option Explicit

Private Sub Worksheet_Activate()

Dim HiddenRow&, RowRange As Range

'< Set the column that contains data
Const DataCol As String = "A"

'*****************************

ActiveWindow.DisplayZeros = False
Application.ScreenUpdating = False

For HiddenRow = 1 To ActiveSheet.UsedRange.Rows.Count

'Take the Length of the value in column A of each row
If Len(Range(DataCol & HiddenRow).Value) < 0 Then
'there's something in this row - don't hide
Rows(HiddenRow).EntireRow.Hidden = False
Else
'there's nothing in this row yet - hide it
Rows(HiddenRow).EntireRow.Hidden = True
End If

Next HiddenRow

Application.ScreenUpdating = True

End Sub


"plumfin" wrote:

I am designing a price list, which different branches will use different
options on, ie, some will offer products A and C, but not product B; others
will offer all 3. I am struggling to work out how to design this as a macro.
The idea would be that if they "tick" A, B and C, then they would then be
promted to put in some prices, which would in turn generate further
discounted price lists. Any ideas? thanks, Kelly

I am struggling to leave all the cells blank, ie, I have some showing a
negative number when I really want them to be blank. Do you know how I can
do this? Also, where does the General Field start on the VBA, and do I need
to edit any of your text with anything at all? Many thanks, I will get this
done eventually!

"Cerberus" wrote:

Sorry, you don't need the Option Explicit part. You would want to go in to
the VBE (Alt+F11) and click on the sheet you want to show only the cells that
have info. You will find the sheets in the Microsoft Excel Objects folder in
the VBAProject area on the left hand side of your screen. Once you "click"
on the sheet, paste the code in the General field. With this code, once the
sheet has been opened all your blank cells will be hidden.

"plumfin" wrote:

Thanks Cerberus, I must be having a dim day - what is Option Explicit? Also,
should i copy this text exactly and paste it into my spreadsheet? thanks,
Kelly

"Cerberus" wrote:

I have used this one before, it may work for you also.

Option Explicit

Private Sub Worksheet_Activate()

Dim HiddenRow&, RowRange As Range

'< Set the column that contains data
Const DataCol As String = "A"

'*****************************

ActiveWindow.DisplayZeros = False
Application.ScreenUpdating = False

For HiddenRow = 1 To ActiveSheet.UsedRange.Rows.Count

'Take the Length of the value in column A of each row
If Len(Range(DataCol & HiddenRow).Value) < 0 Then
'there's something in this row - don't hide
Rows(HiddenRow).EntireRow.Hidden = False
Else
'there's nothing in this row yet - hide it
Rows(HiddenRow).EntireRow.Hidden = True
End If

Next HiddenRow

Application.ScreenUpdating = True

End Sub


"plumfin" wrote:

I am designing a price list, which different branches will use different
options on, ie, some will offer products A and C, but not product B; others
will offer all 3. I am struggling to work out how to design this as a macro.
The idea would be that if they "tick" A, B and C, then they would then be
promted to put in some prices, which would in turn generate further
discounted price lists. Any ideas? thanks, Kelly

No problem, Im pretty new at this myself. The general field is the large
blank area in the middle of the VBA page.

Now since Im kinda new to this also, I would write a simple formula that
say something like =IF(A1<1,"",your formula) so your any value less than 1
leaves you a blank cell. Im sure there is a more efficient way but I have
not come across it yet.


"plumfin" wrote:

I am struggling to leave all the cells blank, ie, I have some showing a
negative number when I really want them to be blank. Do you know how I can
do this? Also, where does the General Field start on the VBA, and do I need
to edit any of your text with anything at all? Many thanks, I will get this
done eventually!

"Cerberus" wrote:

Sorry, you don't need the Option Explicit part. You would want to go in to
the VBE (Alt+F11) and click on the sheet you want to show only the cells that
have info. You will find the sheets in the Microsoft Excel Objects folder in
the VBAProject area on the left hand side of your screen. Once you "click"
on the sheet, paste the code in the General field. With this code, once the
sheet has been opened all your blank cells will be hidden.

"plumfin" wrote:

Thanks Cerberus, I must be having a dim day - what is Option Explicit? Also,
should i copy this text exactly and paste it into my spreadsheet? thanks,
Kelly

"Cerberus" wrote:

I have used this one before, it may work for you also.

Option Explicit

Private Sub Worksheet_Activate()

Dim HiddenRow&, RowRange As Range

'< Set the column that contains data
Const DataCol As String = "A"

'*****************************

ActiveWindow.DisplayZeros = False
Application.ScreenUpdating = False

For HiddenRow = 1 To ActiveSheet.UsedRange.Rows.Count

'Take the Length of the value in column A of each row
If Len(Range(DataCol & HiddenRow).Value) < 0 Then
'there's something in this row - don't hide
Rows(HiddenRow).EntireRow.Hidden = False
Else
'there's nothing in this row yet - hide it
Rows(HiddenRow).EntireRow.Hidden = True
End If

Next HiddenRow

Application.ScreenUpdating = True

End Sub


"plumfin" wrote:

I am designing a price list, which different branches will use different
options on, ie, some will offer products A and C, but not product B; others
will offer all 3. I am struggling to work out how to design this as a macro.
The idea would be that if they "tick" A, B and C, then they would then be
promted to put in some prices, which would in turn generate further
discounted price lists. Any ideas? thanks, Kelly

Thanks for the help on the blank cells - just one question on the actual row
formula, should I copy it word for word as you have written it? When I tried
it, it gave me an error and I don't know what to change. thanks, Kelly

"Cerberus" wrote:

No problem, Im pretty new at this myself. The general field is the large
blank area in the middle of the VBA page.

Now since Im kinda new to this also, I would write a simple formula that
say something like =IF(A1<1,"",your formula) so your any value less than 1
leaves you a blank cell. Im sure there is a more efficient way but I have
not come across it yet.


"plumfin" wrote:

I am struggling to leave all the cells blank, ie, I have some showing a
negative number when I really want them to be blank. Do you know how I can
do this? Also, where does the General Field start on the VBA, and do I need
to edit any of your text with anything at all? Many thanks, I will get this
done eventually!

"Cerberus" wrote:

Sorry, you don't need the Option Explicit part. You would want to go in to
the VBE (Alt+F11) and click on the sheet you want to show only the cells that
have info. You will find the sheets in the Microsoft Excel Objects folder in
the VBAProject area on the left hand side of your screen. Once you "click"
on the sheet, paste the code in the General field. With this code, once the
sheet has been opened all your blank cells will be hidden.

"plumfin" wrote:

Thanks Cerberus, I must be having a dim day - what is Option Explicit? Also,
should i copy this text exactly and paste it into my spreadsheet? thanks,
Kelly

"Cerberus" wrote:

I have used this one before, it may work for you also.

Option Explicit

Private Sub Worksheet_Activate()

Dim HiddenRow&, RowRange As Range

'< Set the column that contains data
Const DataCol As String = "A"

'*****************************

ActiveWindow.DisplayZeros = False
Application.ScreenUpdating = False

For HiddenRow = 1 To ActiveSheet.UsedRange.Rows.Count

'Take the Length of the value in column A of each row
If Len(Range(DataCol & HiddenRow).Value) < 0 Then
'there's something in this row - don't hide
Rows(HiddenRow).EntireRow.Hidden = False
Else
'there's nothing in this row yet - hide it
Rows(HiddenRow).EntireRow.Hidden = True
End If

Next HiddenRow

Application.ScreenUpdating = True

End Sub


"plumfin" wrote:

I am designing a price list, which different branches will use different
options on, ie, some will offer products A and C, but not product B; others
will offer all 3. I am struggling to work out how to design this as a macro.
The idea would be that if they "tick" A, B and C, then they would then be
promted to put in some prices, which would in turn generate further
discounted price lists. Any ideas? thanks, Kelly