Posts by hockeynick1721

Background: I am automating a task in which I filter a dataset on one field by many criterion. The field I filter on is a list containing numeric IDs ranging from 100 to 900. There is a specific list of 9 IDs I need to apply to the filter.

Need: To apply multiple Criteria to AutoFilter via VBA.

Dim x As Range
With ThisWorkbook.Sheets(1)
    Set x = .Range("IncomingCriteria")
End With
.
.
.
With ActiveWorkbook.Sheets(1)
    .Range("A:H").AutoFilter Field:=1, Criteria1:=x, Operator:=xlFilterValues
End With

Hello,

I have created databars using VBA but wish to remove them with VBA as well. I cannot just FormatConditions.Delete as I have multiple conditional formats on a specific range. I need to check whether the conditional format is a data bar then delete.

Any help would be greatly appreciated!

Thanks,
Nick

I have a .xlsm workbook that I need to save a copy of but without macros (.xlsx). I am saving it as an .xlsx and fileformat:=xlOpenXMLWorkbook but I keep getting an error stating incorrect function.
I first start off with Activeworkbook.SaveAs Filename:=

What am I doing wrong here?

Hello,

I have created a marco-enabled workbook in 2007 but we moved to 2010. I used the Calendar Control 12.0 in 2007, however we do not have this control installed in 2010. How do I install this control?

Any help is appreciated.

Thanks.

Hey guys,
I have a series of Listboxes that need to be filled. I want to loop through them but I can't figure out how. My list boxes are named Listbox1, Listbox2, etc. I was trying to do Listbox(i) where i represents the listbox number and is a count of the columns.

Here's my code.

For i = 1 To iColumn        
Cells(1, i).Select
        Range(Selection, Selection.End(xlDown)).Select
        ln = Selection.Rows.Count
        If ln = 2 Then
            With UserForm1.ListBox(i)
                .Clear
                .ColumnCount = 1
                .AddItem Worksheets("list").Cells(2, i).Value
            End With
        Else
            With UserForm1.ListBox(i)
                .Clear
                .ColumnCount = 1
                .List = Worksheets("list").Range(Cells(2, i), Cells(ln, i)).Value
            End With
        End If
    Next

Re: Conditional Formatting the largest value with greater than a certain base size

Smuzoen, you are amazing. Thank you, works like a gem.

Re: Conditional Formatting the largest value with greater than a certain base size

Thanks smuzoen. That is the only solution I have come up with. It seems as though there should be a way to do it all within the formatting.

Hi guys,

I have a data set that looks like this.

Score -----6.51----6.72----7.85----8.21
base size ---40------80-----100------25

That is one question, I have about 100.

I need to highlight the largest mean Score that has a base size greater than 30. In the example above "7.85" would be highlighted. Not 8.21 because the base size is less than 30.

Does anyone know how to do this? It seems simple enough that I shouldn't have to write a macro to do it.

Any help is appreciated.

Thanks
Nick

Re: VBA Closing Excel without Save

Thank you. It was that easy.

Re: VBA Closing Excel without Save

What i want to happen is that Excel will close without asking to save.

Re: VBA Closing Excel without Save

Full Screen Mode if not turned off will affect other workbooks.
I do not want it saved.

Currently this is my code

Private Sub Workbook_BeforeClose(Cancel As Boolean)    
Application.DisplayFullScreen = False
    Application.DisplayAlerts = False
    ActiveWorkbook.Close
    Application.DisplayAlerts = True
    Application.Quit
End Sub

I have Full Screen Mode on, but want it to be false once closed (not affecting other workbooks) and I don't want the save prompt to appear. And I want excel to close, not just the workbook.

Re: Joining data with different DateTimes series in Excel or Access

How do you do this? This is amazing!

Re: Joining data with different DateTimes series in Excel or Access

Thanks for sample. Helps so much in determining what you are trying to do and allows me to test.

As long as all sheets have the same structure and there are only data sheets, this will work. Just paste it into a new module and run it.

Option Explicit
Sub CombineAndSort()
Dim k As Long
Dim iColumn As Long
Dim jRow As Long
Dim kTabs As Long
Dim datarows As Long


kTabs = ThisWorkbook.Sheets.Count
jRow = 1
iColumn = 1


'Create sheet that will hold data
Sheets.Add.Name = "All"
Sheets("all").Select
Sheets("All").Move after:=Sheets(kTabs + 1)


'put in the Date Time header
Worksheets(1).Range("a1").Copy
Worksheets("all").Range("a1").PasteSpecial


'loop through each sheet and collect data
For k = 1 To kTabs
datarows = Worksheets(k).UsedRange.Rows.Count
    Worksheets(k).Select
    Worksheets(k).Range(Cells(2, 1), Cells(datarows, 1)).Copy
    Worksheets("All").Cells(jRow + 1, 1).PasteSpecial
    Worksheets(k).Range(Cells(2, 10), Cells(datarows, 10)).Copy
    Worksheets("All").Cells(jRow + 1, iColumn + 1).PasteSpecial
    Worksheets("all").Cells(1, k + 1).Value = Worksheets(k).Name & "_Water Elevation"
    iColumn = Worksheets("All").UsedRange.Columns.Count
    jRow = Worksheets("All").UsedRange.Rows.Count
Next k


'Sort the data
Worksheets("All").Select
Range(Cells(1, 1), Cells(jRow, iColumn)).Select
ActiveWorkbook.Worksheets("All").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("All").Sort.SortFields.Add Key:=Range("A1"), _
    SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("All").Sort
    .SetRange Range(Cells(1, 1), Cells(jRow, iColumn))
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With


End Sub

Let me know if you need anything else.

Re: Help in understanding dynamic vs fixed type of code

Sub ByGroupRank() 


     ' ByGroupRank Macro
     ' Macro recorded 5/19/2009
dim jrow as long 'long is a long number
dim iColumn as long 


jrow = worksheets("NAME").UsedRange.Rows.Count ' This will count the column with most rows used.
iColumn = worksheets("NAME").UsedRange.Columns.Count ' This will count the row with most columns used.    


    Range(cells(3,2),cells(jrow, iColumn)).Select  'Cells is the numeric position of a cell (rows,columns)
    Selection.Sort Key1:=Range("bk4"), Order1:=xlAscending, Header:=xlYes, _ 
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ 
    DataOption1:=xlSortNormal      
End Sub

This should do the trick. As long as there isnt anything other than data rows 3 and down, it will be fine. Let me know if you need anything else.
Remember to replace NAME with sheet name (keep the quotes).

Re: Joining data with different DateTimes series in Excel or Access

Can you provide a sample workbook and do you just want the water evaluation column for each?

Re: Joining data with different DateTimes series in Excel or Access

I can think of a way to do it, but there would be multiple rows with the same time stamp. i.e.
12/15/2011 9:30
12/15/2011 9:30

Re: Help in understanding dynamic vs fixed type of code

This is sorting on column "bk". Are there headers in rows 1 and 2? Will the data only expand by Rows? Can you provide a sample workbook?

Hello,

I have been trying to work with the event handler WorkBook_BeforeClose but cannot get it to change when my button is clicked (btnClose) to close the workbook. Can someone explain to me how to get this to work? Thank you.

Re: Condensing/Simplifying an argument with loops and rept

YES! IT WORKS! How is it working/What is it doing?
Thank you so much!