EDITING A RANGE FOR FILTERATION

[Guest]

I have constructed a database spreadsheet to be distributed the different
branches. The formulas (which are frozen/locked cells) range in a column for
calculation of interest (suppose C1:C20000). The cases are coming upto 15000
in total listed in column "A". I have permitted the users to filter for
sorting out cases pertaining to their particular branch, branch names listed
in Column B.

Now if a branch, for instance, filters out its cases, the extra rows (for
further addition of cases, i.e. 15001:20000) also hide.

How can I opt to allow a branch for filtering data based on the logic of
blank rows (except for Column C, i.e. calculation formula) being shown
alongwith their branches' records?

Thank you all 4 your kind consideration.

 

[Roger Govier]

Hi Faraz

What version of XL are you using?
In XL2003 the List was introduced Data>List>Create List
If you use this, then you do not need to set your range to 20000, as the
List will "grow" automatically for you as you add more rows, including
the replication of any formulas within the List.
Even with Filter set, it will create a new record in the next available
row.

If you have set the range to be 2000, then when filtering is applied,
data would be added at row 20001.
One way around the problem would be to insert a row above your data, and
use that as the input row.
Then use the following macro to move that data to the end of the list
after you have entered the data. You could assign it to a button on the
sheet.

Sub MoveToEnd()
Dim lastrow As Long, Myrange As Range
Set Myrange = Range("D1:M1") '<===Set as appropriate
lastrow = Cells(Rows.Count, 1).End(xlUp).Row
myrange.Cut Range("D" & lastrow + 1)

End Sub

Set Myrange appropriate to the cells you wish to copy.

You can copy the code and paste it into your Visual Basic Editor
(VBE) in a Standard Module located in your file.

To do this,

Alt + F11 (open VBE)
Ctrl + R (open Project Explorer)
Select the file name on the left
Insert > Module
Paste code in Module

 

[Dave Peterson]

You have a response at your later post.

I have constructed a database spreadsheet to be distributed the different
branches. The formulas (which are frozen/locked cells) range in a column for
calculation of interest (suppose C1:C20000). The cases are coming upto 15000
in total listed in column "A". I have permitted the users to filter for
sorting out cases pertaining to their particular branch, branch names listed
in Column B.

Now if a branch, for instance, filters out its cases, the extra rows (for
further addition of cases, i.e. 15001:20000) also hide.

How can I opt to allow a branch for filtering data based on the logic of
blank rows (except for Column C, i.e. calculation formula) being shown
alongwith their branches' records?

Thank you all 4 your kind consideration.