• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

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

cmissal

New Member
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
 
Casey

If you are making the results table in VBA why not make the whole pages including text?
 
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
 
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.
 
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
 
Back
Top