ListFillRange accept the range's address not the range itself.

Sub ComboBox1_DropButton_Click()

    Dim i As Range

    With Sheets("Pipe 16")
        Set i = .Range("G5:G" & .Range("G" & .Rows.Count).End(xlUp).Row)
    End With
    Me.ComboBox1.ListFillRange = i.Address

End Sub

• Use a IFERROR(cellReferenceHere;functionName())

• Write it as Public Function on VBA.

=IFERROR(0/0;doSomething())

Public Function doSomething()
    doSomething = "Done"
End Function

Range("A2:A" & Range("C5").Value + 2).Select

Resize

Range("A2").Resize(Range("C5").Value + 2)

Thankyou that really helped. Just the small things sometimes - my first venture in to vba and macros and it made my project possible, without needing to spend tens or hundreds of hours researching or learning the language to get the job done.

Here's one way of doing it without VBA:

(all instructions to be done on the sheet with the items list unless otherwise stated):

=NOT(ISERROR(SEARCH('Choose Items'!$A$2,A2)))

=IF(C2,1,0)

=IF(C3,D2+1,D2)

=IFERROR(INDEX($A$2:$A$9,MATCH(F2,$D$2:$D$9,0)),IF(F2=1,"No match",""))

=OFFSET(Items!$G$2:$G$9,0,0,MAX(Items!$D$2:$D$9,1),1)

• Use the new ItemsDynamic as the source of your dropdown in the field validation

You can use a dynamic range name to pull the sub categories if

• there is a two-column table with categories in one column and sub categories in the other column

• all main categories are repeated (no blanks)

• the table is sorted ascending by the main category column.

A dynamic range name can then be built with a formula along the lines of

=INDEX(Sheet1!$B:$B,MATCH(Sheet1!$E$2,Sheet1!$A:$A,0)):INDEX(Sheet1!$B:$B,MATCH(Sheet1!$E$2,Sheet1!$A:$A,1))

So, you only need to work out how to get this two-column table created dynamically from your data entry tool.

With a tiny bit of massaging, this worked like a charm! I figured it had to be some kind of combo with MATCH. I greatly appreciate it.

lastRow = WorksheetFunction.CountA(Range("A:A"))
 Set sourceRange = ActiveSheet.Range(Cells(1, 1), Cells(lastRow, 1))

counts the number of none empty cells, but it may not give you the last cell (logical error). Like KazJaw wrote above, it might be also the reason for a possible error.

A better way would be to use (it replaces the above two lines):

Set SourceRange = ActiveSheet.Range(Cells(1, 1), _
                         Cells(ActiveSheet.UsedRange.Rows.Count, 1))

Try it, and if it fails, please post the error message.

A simple way to copy the error is just click on the error messagebox, then press CTRL+C, and it should copy the error details in plain text to the clipboard, so you could paste it here.

your idea is wrong. Original and your proposal for Set sourceRange= could not be used interchangeably, both could refer to totally different ranges.

still not correct but much better... you should use something like ...cells(rows.count, "A").end(xlup)...

Edited to make it clear: As a result you will copy each column separately. Column-to-copy order will be kept. If you keep constant destination range rules each column will be copied into appropriate range with the same order.

Sub Answer()
    Dim shtData As Worksheet
    Set shtData = Worksheets("Data")
    Dim rngDataRowCount
    rngDataRowCount = shtData.Range("A1048576").End(xlUp).Row
    Dim rngData As Range

    '<--start of modification
    Dim rngCol As Range
    Set rngData = shtData.Range("A1:I" & rngDataRowCount & _
                    ",N1:N" & rngDataRowCount & _
                    ",P1:R" & rngDataRowCount)

'<---variant A- keep columns separated into destination range
    For Each rngCol In rngData.Columns
         rngCol.Copy rngCol.Offset(0, 20)   'here, 20 columns to right
    Next
**'here edited**

'<---variant B- copy columns into new location, united, no loop
         rngData.Copy Range("zz1")           'here, set destination as single top-left cell
End Sub

One tip- if you copy your columns into other Range use syntax as presented Range.Copy destinationRange

That assumes that my columns are always a fixed amount of columns (here 20) apart right? In that case what is the benefit of: ("A1:I" & rngDataRowCount & _ ",N1:N" & rngDataRowCount & _ ",P1:R" & rngDataRowCount) Over: ("A1:R" & rngDataRowCount)

As I understand you don't have continuous column range, in Range("A:R") some are missing, you take some of them only. The benefit is that you can set columns you need in rngData variable and copy than each column separately from that range only. There could be some alternative techniques which depend of the way you prefer to set rngData.

Oh I see - If I set the range that way is there any way I can copy all the columns in one go rather than separately? Thanks for the help by the way

see the code after edition. There are two options now for you- to copy as separated or united columns. hope it helps you... Use either variant A or variant B, not both at the same time.

If there is one cell of your range which is always within your table, like A1 would be always top-left corner of the table. And if there is continuous range of cells until the end of your table you could use .CurrentRegion property in this way:

Set rng = dataSheet.Range("A1").CurrentRegion

Sheets("Raw Data").Select
Range("B3:B7").Select
Selection.Copy
Sheets("Sheet1").Select
Range("D2").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
    False, Transpose:=True

Sheets("Sheet1").Range("D2").Resize(5, 1).Value = _
 Application.Transpose(Sheets("Raw Data").Range("B3").Resize(1, 5).Value)

Taking that as a starting point, you can declare some variables (or better, use Constants) for the "5", etc, but you'd need to add some more details to your question, including what dtermines where the next "paste" point is. you mention multiple sheets but it's not clear how those are involved in the operation.

Sub Tester()

    Const DATA_PTS As Long = 5

    Dim rngCopy As Range, rngPaste As Range

    'start point for source data
    Set rngCopy = Sheets("Raw Data").Range("B3").Resize(DATA_PTS, 1).Value

    Do While Application.CountA(rngCopy) > 0

        'Set rngPaste = ? 'what determines where data is copied to?

        rngPaste.Resize(1, DATA_PTS).Value = _
           Application.Transpose(rngCopy.Value)

        'next source range
        Set rngCopy = rngCopy.Offset(DATA_PTS, 0)
    Loop

End Sub

Thanks for the comment, Tim. I ran what you gave and it's a good start, but not quite what I'm looking for. I'm certain that it has more with in inability to communicate what I want. This may help

Well, I can't post what I want because my reputation isn't high enough to post pictures. I'll work on increasing that.

Something like this should work (this code adds new CF to current region of A1 range and applies formatting when row in column A doesn't contains string from StrToFind variable):

Sub test()
   Dim r As Range
   Dim StrToFind As String

   StrToFind = "abc"
   Set r = Range("A1").CurrentRegion
   With r
       .FormatConditions.Add Type:=xlExpression, Formula1:= _
            "=ISERROR(FIND(""" & StrToFind & """,$A1))"
       .FormatConditions(.FormatConditions.Count).SetFirstPriority

       With .FormatConditions(1)
           With .Borders(xlLeft)
               .LineStyle = xlContinuous
               .ColorIndex = 0
               .TintAndShade = 0
               .Weight = xlThin
           End With
           With .Borders(xlTop)
               .LineStyle = xlContinuous
               .ColorIndex = 0
               .TintAndShade = 0
               .Weight = xlThin
           End With
           With .Borders(xlBottom)
               .LineStyle = xlContinuous
               .ColorIndex = 0
               .TintAndShade = 0
               .Weight = xlThin
           End With
           With .Borders(xlRight)
               .LineStyle = xlContinuous
               .ColorIndex = 0
               .TintAndShade = 0
               .Weight = xlThin
           End With

           .StopIfTrue = False
       End With

   End With

End Sub

1) depending on your local settings formula "=ISERROR(FIND(""" & StrToFind & """,$A1))" may be wrong, in that case you should use "=ISERROR(FIND(""" & StrToFind & """;$A1))" (look at seperators in formula just before $A1: it is either a comma or a semicolon).

3) the 'normal' format of your table should be without borders (borders will appears with help of CF only in case when row in column A doesn't contains abc)

No access to my computer at the moment but very excited to try your code simoco. Will this work with multiple strings? (Eg. "abc" or "def"). Thank you very much for your detailed reply. I will let you know how I get on as soon as I've tried it.

"=AND(ISERROR(FIND(""" & StrToFind & """,$A1)), ISERROR(FIND(""" & AnotherStrToFind & """,$A1)))"

StrToFind

AnotherStrToFind

Unfortunately I cannot get this code to work. I played around with it for quite a while but have had no luck. I have managed a rather ugly work around with the recorder that should suit most of what I need. Thanks heaps for your quick and detailed solution simoco.

Hi, you can use "offset" combined with counta to give dynamic number of rows:

RefersToR1C1:= "=Offset('February 2015!R7C26,0,0,counta('February 2015!R7C26:R10000C26) - 1,1)"

As for the names per sheet thing... I can't say I really use "worksheet" scoped names, they seem to give me a headache... but try something like this: i.e. instead of adding name from the workbook object, add from the worksheet:

for each wsSheet in thisworkbook.sheets
    wsSheet.names.add Name:="monthlyclaim", RefersToR1C1:= "=Offset('" & wssheet.name & "'!R7C26,0,0,counta('" & wssheet.name & "'!R7C26:R10000C26) - 1,1)"
next

Rinse and repeat for other name... So kinda conceptually different but same effect: any formulas on those sheets that reference "monthlyclaim" will match a worksheet scoped name first.

Won't this give the wrong answer if there is a blank cell in the list?

true... Dan's answer below might be more suitable in that case (if there are blanks)

I was thinking that if they were all numbers you could use MATCH(9.999999999999999999E+307,Z7:Z26) to get the row of the total instead of counting entries.

or if there's a total row there's probably some text "Total:" or something somewhere you can search for or match on

If I see that correctly then your problem is that you want map the cell numbers to their cell names; similar to this:

ActiveSheet.Cells(1,x).Formula = "=" & Chr(Ord("A") + 1) & y & "*" & x

Keep in mind that this will only work for the first 26 columns, after that you'll need to find a better solution.

Private Const TEMPLATE As String = "=INDEX({0},MATCH(1,({1}={2})*({3}={4}),{5}))"
Private Const MATCH_TYPE = 0

Public Function TestIndexMatch1(ByRef outputRange As Range, _
                                ByRef nameCriteria As Range, _
                                ByRef dateCriteria As Range, _
                                ByRef nameRange As Range, _
                                ByRef dateRange As Range)

    On Error GoTo Err_Handler
    Err.Number = 0

    Dim originalReferenceStyle
    originalReferenceStyle = Application.ReferenceStyle
    Application.ReferenceStyle = xlR1C1

    Dim myFormula As String
    myFormula = Replace(TEMPLATE, "{0}", outputRange.Address())
    myFormula = Replace(myFormula, "{1}", nameCriteria.Address())
    myFormula = Replace(myFormula, "{2}", nameRange.Address())
    myFormula = Replace(myFormula, "{3}", dateCriteria.Address())
    myFormula = Replace(myFormula, "{4}", dateRange.Address())
    myFormula = Replace(myFormula, "{5}", MATCH_TYPE)

    TestIndexMatch1 = Application.Evaluate(myFormula)

Err_Handler:
    If (Err.Number <> 0) Then MsgBox Err.Description
    Application.ReferenceStyle = originalReferenceStyle
End Function

thanks for the idea. I tried your code and sheet (incld'g naming conventions and range values) exactly as you set it up but am still getting #VALUE! error. Looks like you are using the international version but I don't think that would create an error. Any thoughts on why it might not be working for me? Thx again!

Quite unsure exactly what it is you require but here's a solution. It's worth noting that both the ColorIndex and Color properties are not necessarily zero with no fill, so if you just change blankCell to a cell with the fill which you define to be blank you'll be good to go.

You need to extract a function here. Something like this:

Private Function IsPresentInRange(ByVal source As Range, ByVal value As Variant) As Boolean
    IsPresentInRange = Application.WorksheetFunction.CountIf(source, value) > 0
End Function

And then you need a way to figure out what ranges you need to give it for a source parameter - that can be a function of its own, or you can hard-code them somewhere; basically you want to have a concept of a group of ranges to call that function with - this would be the simplest:

Private Function GetSourceRanges() As Collection
    Dim result As New Collection

    result.Add Range("D:D")
    result.Add Range("F:F")
    result.Add Range("H:H")
    'maintain this list here

    Set GetSourceRanges = result
End Function

Ideally you would have some logic coded there, so that you don't need to manually add ranges to that collection every time.

And then you can just iterate these ranges and determine if you get a count > 0 for all of them:

Dim sources As Collection
Set sources = GetSourceRanges

Dim result As Boolean
result = True

Dim sourceRange As Range
For Each sourceRange In sources
    result = result And IsPresentInRange(sourceRange, Cells(x, firstcolumn).Value)
Next

If result Then
    ' whatever you had in that If block
End If

Ok I tried your idea and it does work brilliantly as long as the data columns set as GetSourceRanges are populated. However if a column (for example H:H) doesn't exist the function won't return anything. I'm guessing the GetSourceRanges function needs to create the list of Ranges before the rest of the code runs based on the available data columns.

Give the cell a range-name (a defined name). Click in the sum's cell and go to the Formulas tab (Excel 2007/2010), Defined Names group and click Define Name. Type a name (without spaces) and click OK.

Open both books, begin a formula in the other book with '=', click to the other book and click on your named-cell. Excel will use the defined-name in the formula.

The defined-name will move with the cell, when a column is inserted.

Range("B2", range("B2").end(xlToRight).End(xlDown)).select

By the way, there could be plenty of other solutions with similar result. I've tried to do it as short as possible (based on information provided in question).

Thanks for the reply KazJaw. But it doesn't select the whole thing column wise.

I would need to see the structure of your data/table to give more accurate help. Could you upload a screen shot or document somewhere in internet and give a link here?

When you are continuously doing a repetitive exercise which involves excel/system to process multiple events then it is advisable to separate them using DoEvents.

For example in your case, you need to give enough time for Excel to copy the image into Clipboard and then paste it back.

Sheets("Sheet1").Range("ToCopy").CopyPicture

Doevents '<~~ This gives enough time for the pic to be placed in Clipboard

Sheets("Output").Paste

DoEvents '<~~ This gives enough time for the pic to be pasted

In more technical terms, DoEvents surrenders execution of the macro so that the operating system can process other events. The DoEvents function passes control from the application to the operating system.

DoEvents

So I spent A little more time researching today and finally got it to work. the script is posted below:

Sub TransposeData()
'
' TransposeData Macro
' Traspose the data on sheet1 to sheet2 to allow for mail merge
'

Sheets("Sheet1").Select

Dim sourceRange As Range
Dim lastRow As Long

lastRow = Cells(Rows.Count, "A").End(xlUp).Row

Set sourceRange = ActiveSheet.Range("A1:C" & lastRow)

sourceRange.Copy

Sheets("Sheet2").Select

Cells(1, 1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,    SkipBlanks:=False, Transpose:=True

' Keyboard Shortcut: Ctrl+Shift+T
'
End Sub

Thanks El Scripto and KazJaw for your suggestions, much appreciated.

As best I could tell after doing further research, Excel's INDIRECT function simply doesn't work with dynamic ranges. There might be a clever way to get around using INDIRECT and sticking to the non-VBA Excel world, but I'm unaware of such a way. Instead, I ended up creating a user-defined function very similar to the one described here. I altered my main formula to read =VLOOKUP(B2,DINDIRECT("ExampleRange"&C1),2,FALSE), where DINDIRECT is the name of the VBA function I created.

The only downsides (which may or may not be downsides depending on how you look at it) to this alternative is that the workbook must be saved as a macro-enabled workbook and the use of a custom function isn't very self-documenting and requires a little explanation to other users. All things considered, though, this was an acceptable solution for me.

For the link-averse, here's the code:

Public Function DINDIRECT(sName As String) As Range
     Dim nName As Name

     On Error Resume Next
          Set nName = ActiveWorkbook.Names(sName)
          Set nName = ActiveSheet.Names(sName)
     On Error GoTo 0

     If Not nName Is Nothing Then
          Set DINDIRECT = nName.RefersToRange
     Else
          DINDIRECT = CVErr(xlErrName)
End Function

Note: Although this solution worked, I'm not going to accept my answer because I don't want to discourage others from posting better solutions. Also, I'm new to the site, so sorry if I'm breaking any etiquette codes by answering my own question...I just thought I'd share the exact solution that I used in case others find it useful.