Close Window   
Free Ebook
Get Your Free Excel
ebook!
Our Top 15 Excel Tutorials
Instant Access!
E-mail:
Excel Forum

Lock Rows For Filter

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

I feel like this should be really easy and I am just out of it today but I cannot figure this out. I want to lock a row and filter (with filter,not sort, tool) the other rows. I attached an example

The row in RED I want to never be changed or added in the filter, but I want row 2 and 1 to filter together. So I want (in this case) the flavor to filter with the same number. SO i guess the filter process would be dependent upon the total number of votes. Any ideas? Thanks!

I had to create the information in tables and went from there. Got it. Thanks!

View Answers     

Similar Excel Tutorials

Freeze or Lock Specific Rows and Columns when Scrolling in Excel
Prevent specific rows or columns from moving when you scroll through a spreadsheet in Excel. This allows you to kee ...
Introduction to Using Filters to Refine Data in Excel
Filtering allows you to hide rows of data which you are not interested in so that you can easily look at the rows y ...
Filter While Leaving Original Data Intact in Excel
How to filter in Excel by copying the filtered data to a new location without doing anything to the original data ...
Automatically Lock Certain Cells in Excel using a Macro
This macro allows you to have a cell automatically locked after a user enters something into it or into another spe ...
Count the Visible Rows in a Filtered List in Excel
How to use the COUNT function on a filtered list of data so that hidden rows are not included in the count. To do ...
Filter and Sort Data on Charts in Excel
Create a dynamic chart in Excel that displays only the data you want. You can filter it and sort it in order to dri ...

Helpful Excel Macros

Display Filter Arrows in a Table or Data Set in Excel - AutoFilter
- This free Excel macro allows you to apply filter settings to a data set, list, or range of data in Excel. This will not
Display the AutoFilter Criteria Applied to a Data Set in a Cell in Excel - UDF
- This free Excel UDF (user defined function) allows you display the filter criteria that has been applied to a data set i
Filter Data to Show the Bottom 10 Percent of a Data Set in Excel - AutoFilter
- This free Excel macro filters a data set to display the bottom 10 percent of the data set in Excel. This is a great mac
Filter Data in Excel Without the Filter "Arrow" Appearing in the Filtered Column - AutoFilter
- This Excel macro filters data in Excel without the filter "arrow" appearing in the column that is being filtered. This
Filter Data Sets on Multiple Columns with Multiple Criteria at Once in Excel - AutoFilter
- This Excel macro allows you to filter a data set on multiple columns and criteria at once. This means that you can filt

Similar Topics







I'm attempting to filter columns E, G, and I all at the same time. I need to
display rows that have values greater than 0 in any one of those three
columns. So far I can only filter progressively. If I filter column E and
then filter column G, the second filter is only applied to the results of the
first filter.

Does anybody know if it's even possible to do what I'm trying to do?



Hi!

I have a question regarding filtering of columns, hopefully someone is able to put me on the right track. I have set up Excel 2007 so that I can click on the drop-down menu in each column to filter them.

However, in my data set there is one blank row separating two set of rows. Now, when I apply the filter on a column, all the rows above the blank row filter correctly, but all the rows below stay un-filtered.

I guess Excel only looks at the consequtive rows, then stops when it hits a blank row. Is there any way of applying the filter beyond a blank row (i.e. the whole column)?

Any advice is higly appreciated.


I have a long list of item descriptions that I need to filter down to descriptions containing words like "valve" or "ring" or "root" or "epic" (17 key words in all). The basic custom filter looks like it only lets me filter for 2 words.

I have some experience working with advanced filter for things like >100 but that's about it. Any help with this would be so appreciated.


Is it possible to apply a filter to multiple sheets within the same Excel workbook, using the same filter criteria?

For example, suppose you have tables of data on 3 different tabs, all having the same headings. Suppose one of the headings is "gender". If you apply a filter to the table on Sheet 1, to only show the rows containing the word "female", is it possible to have the tables on Sheets 2 and 3 automatically update to only show the "female" rows? Or would you have to manually update the filter criteria in Sheets 2 and 3?

I hope this makes sense. Thank you in advance for any help.


Hi,

I made a budget with many narrow columns and have turned the data into a table. I will only ever filter the data with the left-hand most column. However, all the other columns are filled each with the grey drop-down filter arrow button, and I can no longer see the headings of these columns and frankly seeing 50 little unused arrow buttons is rather ugly. Is there any way to simply remove the filter drop-down buttons on some columns or even all of them, just leaving a filter button at the heading of the first column? I know I can hide the headings, but then I lose sight of the first filter arrow that I use.

Sorry to ask such a bizarre question, but I appreciate your help.

Thanks


Hi there,

I am currently using 'Activecell.Offset(1,0).Select' to move down one cell at a time when I click on a button.

The problem I have now is that if someone was to filter by something then the 'next cell down' could be hidden behind the filter (by that I mean it didn't meet the filter criteria).

Is there anyway to move down to the next row, even if that row does not follow on Sequentially .

Any help would be greatly appreciated.

Regards,

James


Hi
I am using Excel 2007 and suddenly I can no longer filter by color. The option is greyed out and so is the sort by Color option. Does anyone have any idea why this would happen?

Thanks in advance.


Hi,

I am using

ActiveSheet.ShowAllData

in a piece of VBA and it causes an error if the sheet does not have a filter applied! Is there an if statement I can use to check if the data is filtered?

Thanks - this site is an absolute life saver!

Rich


i know how to use the data filters vertically but have been wondering if its possible to filter data horizontally so i could put a filter on, say column c and sort the data across the sheet rather than down. if its not possible i will find another way to do what i want but this seems to be just what i would need. does anyone know if it can be done?


I am looking for the code to select the visible data after applying a data filter. Actually I know how to select the data after applying the data filter but the issue is I am not able to exclude the header row and give the target range as used (non-blank) rows only!!

I am using below code to Select the Visible rows in the target range:

Code:

Range("A:p").SpecialCells(xlCellTypeVisible).Select


Problems in this code a

1) after applying the filter, while selecting the data it is selecting all the rows in given range till last row on the workbook. I need this to select the the data only till the last used row in the given range.

2) It is not possible to provide the address of the first row after we apply the filter since the first row address may change depending on the values in the table.

E.g. 1st time when I am running the macro the first row in the visible filtered data is starting at Cell address A4 and next time when I will run the macro it may be A6

3) The Code is also selecting the 1st row which is a header row. How can we exclude it from selection.

Some one please revert with the solution.
Thanks in advance.


After you filter out data, how to you re-number the row numbers so they are, again, consecutive?


Hi there. I'm using Excel 2007 and often use filters to find inconsistent data (mis-spellings, etc.) and then clean up the data using the fill handle to fill in correctly formatted values. I'm finding that, with a simple filter on, dragging over cells hidden by the filter changes the values in the hidden cells too. This is something I don't remember from my last version of Excel. I'm wondering if I've mis-remembered how this control works, or if there is a setting I can't find.

My goal is to drag and fill (or paste) in a colum with a range of rows wiht some hidden (filtered out) rows, and have the filtered out data be unaffected, if that makes sense.

Thanks!

T


I am trying to filter a list of records by the value in a cell and then (for the filtered result(s)) change the value in column Y just for the filtered results. This is the code I have at present which filters the records OK but I can't fathom what I need to do to ensure I only chnage the value of the cell(s) in column Y?

[CODE]ActiveSheet.Range("$A$1:$DC$5000").AutoFilter Field:=1, Criteria1:="GI255"
'After filtering I want to change the value in column Y for only the filtered entries CODE]


Hi everyone,

I found an excellent macro this morning that allows the user to filter a pivot table based on the value found in a specific cell. This cell essentially acts as a search bar, allowing the user to type in what they are looking for rather than select it from a drop-down list.

The macro works perfectly for my purposes except in one regard: I can no longer perform a "show all" filter. If I leave the "search bar" cell blank, the pivot table shows nothing. I'm sure that there is an easy fix for this but I'm still learning the basics of how to write and use macros. How can I change the code so that when I leave the cell blank, the pivot table shows all? The search bar cell is D2.

Thanks for your time!


When I auto filter a spreadsheet, I can't figure out how to delete or
eliminate from the sheet the filtered-out rows. So when I refilter the sheet
with new items to get rid of, the old filtered items come back. I've tried
copying the range to another file, but I always get all the old data in the
new file.



Still learning VBA - I am trying to delete an entire row based on a condition in one cell in the row. Typically I would just filter on that value and delete the rows, but I am not sure if that is a possibility in VBA code. Can you provide the code if not too complex.

Select Cell A1 if value is 100 delete entire row, else skip to next row. Then loop through each row in the spreadsheet till all rows with selected cell equal to 100 are deleted.


Hi all -

Im very familiar with how to write a macro to automatically autofilter a list based upon a Cell Value (i.e. Range =(A1)...etc.).... but what I cannot figure out how to correctly write is a way to have Excel automatically autofilter a list for any row that CONTAINS the Cell Value, instead of just the exact value.

Cell Value = "birds"
Example -- I need to filter every row so that I see every row that CONTAINS "birds" in the character-string, not just the row that = "birds".

a) is this possible?

If not, I'd like to know also so I can stop attempting to guess (-;
Thanks!!!

Matt

Hi All,

Random question i have a large Excel Workbook (which is protected) and has over five sheets on it - however one sheet has randomly decided not to scroll... yes i know sounds random! If i use the cursor and down arrows the selection just disappears off the screen.
The page will scroll if i filter by one field, but not if i select (All) for all filters.
All other worksheets scroll fine.
Any suggestions?

Thanks in advance.
Cheers Kaite


I need to lock consecutive rows together before sorting. e.g. A1 contains a name and A2 is blank: B1 contains data related to name in A1 - so does B2, and so on. Therefore need to lock rows 1 and 2 together, then 3 and 4 together and so on, but sorting on the data in the first cell of the group e.g. A1


Is there any way to automatically lock in the date after you pull it up with the TODAY function? Or is there another function that will do what I'm trying to do?

I want it to automatically fill in today's date, when a certain empty cell has a value put in, then freeze there.

Thanks in advance!


I have searched and read all the help files. I find the properties of
an object, I see how I can "lock", "size and move with cells" or "not
move with cells". No matter what I select, the object moves off the
screen, when the user, scrolls to the right of the spreadsheet.

Is there a way to lock the position, let's say , in the upper right
corner and have it stay there?

This would be quite useful for an EXIT button, that I have created,
that will close the program without saving (it's a read-only file.)

Thanks to all the wonderful people here that have been so helpful and
give us their valuable insight and time.

Jo




Hello,

Is there a way to create a second worksheet that is based on data from an original sheet that will automatically update when you ADD ROWS to the original sheet?

Linking values between sheets is easy... but I need to be able to set a designated area on my A sheet that I can add rows into which will automatically be added to my B sheet.

Does anyone know of a function that I can apply for this?


Is it possible to have numbers added to the same cell and have excel continue
to calculate the addition for me in that same cell......ex: I have the number
8 in cell d2 and I want to add the number 8 to that cell and have excel add
the 8 to the previous 8 for a total of 16 in the same cell.....the next time
I would add 5, and the total would be 21? Can this be done in a single cell?



Hello everyone, I am new to this forum and have been using excel for about 3 years. I have learned alot but still can not figure out how to make numbers add up in one box like a running total. Meaning, If I have a list and in the list is one cell for each item say like cell a1 is for a can of coke and cell a2 is for a car tire and so on,and i want to keep a running total by adding a number to cell a1 or a2 like say "3 i.e. 3 tires or cans of coke" and later that day i come back and need to add 2 more ......so i want to be able to just click on the cell a1 or a2 and enter the number 2 and the cell would add the number in it to the number im adding to it to show a result of 5 tires or whatever. I am sorry If i am not asking the question in an understandable manor but its the best way I can think to ask it.lol anyways, any help would be appreciated, Thanks in advance.


James


I would like to copy a small table from Word into one cell in an Excel
worksheet. The first column of the table is a list of numbers. I tried
converting the table into text with manual line breaks and tab stops to
divide columns and rows, but that didn't solve my problem.

Excel pastes the data into several rows. When I try to merge them, I
get a warning that the selection contains multiple data values, and
merging into one cell keeps the upper-left most data only.

What I tried that didn't work:
* Formatting the Excel cells as text before pasting the data.
* The various options for "Paste Special." The closest I got was
inserting the table as a Document Object, which could be a workaround,
I guess.
What I am saving for when all else fails:
* The obvious solution of copying row by row into one Excel cell.

The data in the table is information about my dad's medications. I
would like to have reference charts of how to identify the strength of
each tablet by its color and markings. I got the info from the
manufacturers' websites and entered it into tables in Word, which I
would like to copy into a more comprehensive file I am creating in
Excel. The first column of each table is the strength of the tablet,
entered as 1 mg., 2 mg., etc. The subsequent columns describe the
shape, color, and markings. There are 3 tables, each with about 4-5
rows.

Is there a way to copy each one - whether as a table or as text - into
a single Excel cell without losing data?

Many thanks.