Help with Data Filter and Filter Results Table (not range) « Chandoo.org - Learn Excel & Charting Online

Chandoo.org - Learn Excel & Charting Online - Forums » Ask an Excel Question

Help with Data Filter and Filter Results Table (not range)

(5 posts) (3 voices)

Started 1 year ago by cmissal
Latest reply from cmissal

Tags:

cmissal

Member
Joined: Apr '11
Posts: 22

I have a filter in my VBA code which works fine, and copies the output to a second table for me to perform additional steps with.

Range("Table_Data_Current_Wk_Schedule").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range("adminMilestonesFilterCriteria"), CopyToRange:=Range("Data_Current_Wk_Schedule!X1:AM1"), Unique:=False

My challenge is that I want the second (results) table to be between other rows in a instructions worksheet. (currently it is on it's own worksheet)

Example:

Text Row Before
Text Row Before
Text Row Before
blank row
FILTER RESULTS TABLE
FILTER RESULTS TABLE (length varies depending on the # of results from the filter)
Text Row After
Text Row After
Text Row After

Normally, when you add text to a blank row at the bottom of a Table the new row becomes part of the table. The problem I am having is that, when the filter results exceed the length of the results table they overwrite the "Text Row After" rows rather than dynamically adjusting the results table length.

PS. I have searched every keyword I could think of, on multiple occasions, and just can't seem to find a case similar to this to learn from.

Thanks in advance for your help/suggestions!

Casey

Posted 1 year ago #
Hui

Excel Ninja
Joined: Jul '09
Posts: 5,025

Casey
If you are making the results table in VBA why not make the whole pages including text?

Posted 1 year ago #
NARAYANK991

Excel Ninja
Joined: Oct '11
Posts: 4,032

Hi Casey ,

When you have the filter results on a separate worksheet , is this a worksheet which has been created solely for storing the results of the filter , or is it a worksheet which is used for other purposes too ?

If it is the former , then can you not find out the number of rows which are used for the filter results , insert these many rows into the worksheet where you want these results to be copied , do the copy and then delete the unwanted worksheet ?

Narayan

Posted 1 year ago #
cmissal

Member
Joined: Apr '11
Posts: 22

Narayan, The filter results are posted to an hidden worksheet, so I could use your approach and just perform the count row/copy without deleting the sheet when I am done. I will try it.

Hui, The worksheet I want to post the results back to are the 'instructions' sheet for refreshing the weekly dashboard. It has all my data entry fields, VBA triggers (Buttons) and results validation. I don't think this sheet is a good candidate for re-writing the entire page... though there are other pages I want to evolve to this point.

Posted 1 year ago #
cmissal

Member
Joined: Apr '11
Posts: 22

Narayan/Hui,

I have implemented the following code which takes the 'n' rows of my filtered data from the hidden 'calculations' worksheet, inserts 'n' blank rows in my instruction form/worksheeet and pasts the values in the new blank rows. I still need to add the conditional formatting to each row, and 'reset' (delete) the form/table upon the next refresh of the filtered data, but this solves the immediate problem

Please let me know if you see any obvious issues with this code, or obvious improvements.

THanks again for your help and wisdom!

Public Sub CopyAllRows()
Dim TableLength, NextRow, RowsToInsert, LastRow, x As Integer
Dim ThisValue, TestLastRow As String
Dim oSh, pSh As Worksheet

Application.ScreenUpdating = False
Application.Calculation = xlManual
Application.EnableEvents = False

'*** Delete existing Table
'ADD THIS CODE

Sheets("Data_Current_Wk_Schedule").Select
Set oSh = ActiveSheet
oSh.Range("Table_45_Day_Milestones[#All]").Select
TableLength = Selection.Cells(Rows.Count, 1).End(xlUp).Row
TableLength = Selection.Cells(TableLength, 1).End(xlUp).Row

Sheets("Admin Worksheet").Select
Set pSh = ActiveSheet
NextRow = Range("adminInsertFilterHere").Row + 1

'*** Insert Rows on admin worksheet to hold new table
RowsToInsert = TableLength + 1
InsertBlankRows pSh, CInt(NextRow), CInt(RowsToInsert)

'*** Select the destination worksheet and copy the selected ranges from the source workshet. then past to destination
oSh.Range("Y1:Y" & TableLength & ",Z1:Z" & TableLength & ",AO1:AO" & TableLength & ",AP1:AP" & TableLength & ",AQ1:AQ" & TableLength & ",AR1:AR" & TableLength & ",AS1:AS" & TableLength).Copy
pSh.Cells(CInt(NextRow), 1).Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False

'*** Define Range as Table and assign Table Name
LastRow = NextRow + TableLength
ActiveSheet.ListObjects.Add(xlSrcRange, Range(Cells(CInt(NextRow), 1), Cells(CInt(LastRow), 7)), , xlYes).Name = _
"Table_admin_45_Day_Milestones"

Application.ScreenUpdating = True
Application.Calculation = xlAutomatic
Application.EnableEvents = True

End Sub

Sub InsertBlankRows(pSh As Worksheet, nInsertLocation As Integer, nRows As Integer)
pSh.Select
Cells(nInsertLocation, 1).Select

For i = 1 To nRows
ActiveCell.EntireRow.Insert shift:=xlDown
Next i

End Sub

Posted 1 year ago #