Replies

Hi

It would be far more helpful if you uploaded your workbook rather than PDF’s.
I would question why you do this on 2 separate sheets?
If you created an are at the top of your sheet 2 to enter the Part Number required, you could then use either Autofilter or Advanced Filter to display that part only.
Then any amendment you make will be saved against the one and only entry of that part.

Or even:
=IF(AND(LEN($D6)=10,–LEFT($D6,1)<7),$E6,0)

Quite right Rory – why type all those extra zeros!!!!!!

Hi

Noting all that has been said by maudibe and zeddy, you formula can be simplified to

=IF(AND(LEN($D6)=10,–LEFT($D6,4)<7000),$E6,0)

Hi

As an alternative to looping through the rows, you could use the folowing

Code:

Sub DeleteZeroRows()

‘ Delete all rows with Zero in column B of named sheet.
‘ Note this does not remove rows with Blank in column B
    With Sheets(“Sheet1”)
        .Range(“A1:B1″).AutoFilter Field:=2, Criteria1:=”0”     ‘ Field 2 = Column B
        .Range(“A1”).CurrentRegion.Offset(1).SpecialCells(12).EntireRow.Delete
        .ShowAllData
        Selection.AutoFilter
    End With
End Sub

Hi

If you mean that you are wanting to use the Table Structured references then an alternative would be to copy the formula below
and paste to range L4:W20 of your existing sheet.

=Table1[@Total]/3*(Table1[@QTR]=–RIGHT(L$2))

Note: This will still give the rounding differences as opposed to RG’s method – but if it is only Budget figures, this may not matter too much to you.

Quite right, Zeddy. Very sloppy on my part!!!

However, I normally have Option Explicit at the top of all modules I create (here I was just modifying one from Maud), and therefore Dim all of my variables.
There is no need to have the extra variable zAddr, you can just check Target.Address directly

Here is the revised code

Code:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = [g1].Address Then
        Dim c As Range
        Application.ScreenUpdating = False
        If [g1].Value = "" Then
            ActiveSheet.UsedRange.Offset(1).AutoFilter Field:=7
        Else
            ActiveSheet.UsedRange.Offset(1).AutoFilter Field:=7, Criteria1:="*" & [g1].Value & "*"
        End If
        For Each c In Range(Cells(2, 1), Cells(2, 6))
            c.AutoFilter Field:=c.Column, Visibledropdown:=False
        Next
        Application.ScreenUpdating = False
        [g1].Activate
    End If
End Sub

Maud
That’s a nice solution.
However, I prefer to avoid the addition of a Text box.
It can be done with entry to a given cell (I used G1) and the Worksheet change event.
I think this “looks a little neater”, and just deleting the entry in G1 returns to the full list, as opposed to having to clear the filter.

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim c As Range
    Application.ScreenUpdating = False
    If [G1].Value = "" Then
        ActiveSheet.UsedRange.Offset(1).AutoFilter Field:=7
    Else
        ActiveSheet.UsedRange.Offset(1).AutoFilter Field:=7, Criteria1:="*" & [G1].Value & "*"
    End If
    For Each c In Range(Cells(2, 1), Cells(2, 6))
        c.AutoFilter Field:=c.Column, Visibledropdown:=False
    Next
    Application.ScreenUpdating = False
    [G1].Activate
End Sub

Hi Maud, Absolutely no need to apologise. That often happens.

Hi

The caret symbol “^” is used to denote Power.

=2^2 is 2 to the power of 2, or 2 squared which is 4
= 2^3 is to to the power of 3 or 8
= 2^4 is to to the power of 4 or 16

It is a convenient notation as opposed to using the inbuilt POWER() function in Excel
=POWER(2,3) would also result in 8

Hi

Upload your file

Hi
Your explanation is not very clear.
Can you provide a sample file with a better explanation of what you are trying to do?

Hi

Yes that is the case when you are posting between applications.
You will only see that option in the right click menu.
I should have made that clear

Hi

Supposing your data is in row 2, with the first value you are interested in is in in column B, the next in I and so on
then the following array entered formula will do what you want.

{=SUMPRODUCT(–(MOD(COLUMN(B2:CG2),7)=2),–(IF(ISNUMBER(B2:CG2),B2:CG2,0)))}

To array enter a formula use Control+Shift+Enter (CSE) not just Enter.
When you use CSE, Excel will insert the curly braces { } around your formula.
Do not type them yourself.

Hi Simon

You are quite right that there is a remove duplicate function in later versions, I was trying to give a generic solution.
With this particular set of data you are correct, a separator is not required in the concatenation.
However, I do it as a matter of course with all concatenations, because there are occasions when you can get tripped up with different numbers of characters in the concatenated fields.

On the matter of the Countif function, however, you are totally wrong.
Your formula will count the number of occurrence in the whole data set, and that would not provide a list of uniques.
You would not be able to select the rows you wanted to delete, as it would also contain the first occurrence of the value.

The formula I proposed, does not need to be sorted.

Hi

Then try

Code:

Sub PrintProposal()
'
' PrintProposal Macro


Sheets("Proposal").Visible = xlSheetVisible
Sheets("Spreadsheet").Visible = xlSheetVisible
Sheets(Array("Proposal", "Spreadsheet")).Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
IgnorePrintAreas:=False
Sheets("Proposal").Visible = xlSheetHidden
Sheets("Spreadsheet").Visible = xlSheetHidden
Sheets("CustomerData").Select


End Sub