Conditional Formatting For Row Banding Doesnt Work After Autofilter |
Conditional Formatting For Row Banding Doesnt Work After Autofilter - Excel |
|
For obvious reasons, the conditional formatting to shade alternate rows doesn't work when filtered. So I think I need another way of doing it. Luckily, my table is fairly static, rows aren't added or removed. The first column is excluded from the banding. As is the first and last row with data (1 & 67) respectively.
I found a relevant thread here, but the code is beyond my understanding.
I found a relevant thread here, but the code is beyond my understanding.
Excel VBA Course - From Beginner to Expert
200+ Video Lessons 50+ Hours of Instruction 200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
(80% Discount Ends Soon!)
Similar Topics
I know that you can do PASTE > VALUES in order to keep your conditional formatting on an existing sheet, but sadly the people in my office are prone to not understanding this ("It's a bit technical") and so they just pasting blocks of text from elsewhere and lose it all.....
Does anyone have any suggestions about how to maintain the formatting when people just do a traditional C&P ?
Thanks
Does anyone have any suggestions about how to maintain the formatting when people just do a traditional C&P ?
Thanks
Is there a way to use the new conditional formats (data bars, icon sets, etc) in PowerPoint? Basically, I'd like to create a table that shows icons based on data, like in Excel? I know I could paste a pre-made Excel table as a picture, but I'd like to avoid that if possible.
Thanks!
Thanks!
Hi,
how do I select (in VBA) all the rows that were filtered out by autofilter (using VBA code) and delete them leaving just header. I just can't figure out how to select entire rows when the data is filtered...
Thanks for your hints!
how do I select (in VBA) all the rows that were filtered out by autofilter (using VBA code) and delete them leaving just header. I just can't figure out how to select entire rows when the data is filtered...
Thanks for your hints!
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.
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.
Hi,
I use filters a lot at my work. One thing I've noticed is that if I have something filtered and want to copy and paste only what I see from one column to another column right next to it, it will copy what is filtered, but then paste into the unfiltered portion.
Is there some way to copy only what I see when it's filtered, THEN paste only onto the adjancent cells which is also filtered?
For example let's say i have each of the number below in a cell (Fig 1). And then I hide rows 2 -3, so only 1 and 4 is showingI only see 1 & 4 in column A. I want to copy and paste 1 and 4 to the next column over one row so it looks like Fig. 2 when unhidden. Not like Fig. 3.
Fig. 1
1
2
3
4
Fig. 2
1 1
2
3
4 4
Fig. 3
1 1
2 4
3
4
I use filters a lot at my work. One thing I've noticed is that if I have something filtered and want to copy and paste only what I see from one column to another column right next to it, it will copy what is filtered, but then paste into the unfiltered portion.
Is there some way to copy only what I see when it's filtered, THEN paste only onto the adjancent cells which is also filtered?
For example let's say i have each of the number below in a cell (Fig 1). And then I hide rows 2 -3, so only 1 and 4 is showingI only see 1 & 4 in column A. I want to copy and paste 1 and 4 to the next column over one row so it looks like Fig. 2 when unhidden. Not like Fig. 3.
Fig. 1
1
2
3
4
Fig. 2
1 1
2
3
4 4
Fig. 3
1 1
2 4
3
4
How can I make a cell or the font of the cell flash or blink? I assume that
it is a function of conditional formatting. I know it is possible because I
have seen it before but I do not know how to do it.
it is a function of conditional formatting. I know it is possible because I
have seen it before but I do not know how to do it.
Hello
I'm quite experienced Excel user. I've never come across this problem but tinkering in every conceivable way within Excel settings and the solution has eluded me.
I have added a worksheet created elsewhere (it is a form I need printing, with the data coming from 2 sheets I have created from scratch) which has pre formatted cells for Date and Client Name etc.
When I try to reference the cell in this added sheet from my 2 sheets, instead of the result, it always displays the formula, not the result.
I have tried doing it from one of my sheets to reference to this new, and that displays the result and not formula. I can't imagine why it's doing this and I've never seen it happen before.
Formatting cells, giving cells names rather than the usually adequate of reference to the Cell Number doesn't change things. I wonder if I've picked up some legacy protection from the original form but can't see anywhere in the tools etc that's obvious.
There's about 50+ cells that need referencing and I got to get this done for work.
Please help me. This is my first need to post on a Excel Forum as I've always found help or answers from other peeps or internet but this one is making me scratch my head big time.
I'm quite experienced Excel user. I've never come across this problem but tinkering in every conceivable way within Excel settings and the solution has eluded me.
I have added a worksheet created elsewhere (it is a form I need printing, with the data coming from 2 sheets I have created from scratch) which has pre formatted cells for Date and Client Name etc.
When I try to reference the cell in this added sheet from my 2 sheets, instead of the result, it always displays the formula, not the result.
I have tried doing it from one of my sheets to reference to this new, and that displays the result and not formula. I can't imagine why it's doing this and I've never seen it happen before.
Formatting cells, giving cells names rather than the usually adequate of reference to the Cell Number doesn't change things. I wonder if I've picked up some legacy protection from the original form but can't see anywhere in the tools etc that's obvious.
There's about 50+ cells that need referencing and I got to get this done for work.
Please help me. This is my first need to post on a Excel Forum as I've always found help or answers from other peeps or internet but this one is making me scratch my head big time.
I'm trying to use conditional formatting to highlight phone calls that came in between certain hours.
The call times are in the custom format h:mm, although it could easily be changed to an Excel time format. I'm using Excel 2002. I want the cell to have a different fill color if it falls within particular time frames.
Example: If the call came in between 22:00 and 23:59 color is light green.
If the call came in between 23:59 and 08:00 the color is yellow.
Example spreadsheet is attached.
Thanks in advance.
The call times are in the custom format h:mm, although it could easily be changed to an Excel time format. I'm using Excel 2002. I want the cell to have a different fill color if it falls within particular time frames.
Example: If the call came in between 22:00 and 23:59 color is light green.
If the call came in between 23:59 and 08:00 the color is yellow.
Example spreadsheet is attached.
Thanks in advance.
Hello,
I need vba code to Select first visible cell below the header row after applying autofilter on column Q of the data. Can somebody help me on this?
I tried
http://www.mrexcel.com/forum/showthread.php?t=403989
but it does not work. Probably because column Q is filtered to show only blank cells,
Can somebody help me on this?
Thanks,
awagdarikar
I need vba code to Select first visible cell below the header row after applying autofilter on column Q of the data. Can somebody help me on this?
I tried
http://www.mrexcel.com/forum/showthread.php?t=403989
but it does not work. Probably because column Q is filtered to show only blank cells,
Can somebody help me on this?
Thanks,
awagdarikar
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.
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.
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]
[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]
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:
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.
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.
Is there a trick to copy-paste a group of cells into Outlook? I don't want to paste as a picture (shows up as an attachment and is lost when someone else Replies), but formatting is skewed when pasted as an Excel object. Right now I'm recreating the table in Word, then pasting, which doesn't loose formatting.
A specific problem is cells which don't have borders show up with very light grey borders in Outlook. This, I do not want.
TiA
A specific problem is cells which don't have borders show up with very light grey borders in Outlook. This, I do not want.
TiA
In my pivot table I have a lot of cells without any data. The report looks like garbage with all the (BLANK) sohowing up in the report.
I've tried condtional formatting where if the cells = (BLANK) it woulf format them as white -did not work
I tried the pivot table options and clicking on the box "for empty cells show" and set it to 0 then empty but that did not work.
any Ideas???
Thanks
I've tried condtional formatting where if the cells = (BLANK) it woulf format them as white -did not work
I tried the pivot table options and clicking on the box "for empty cells show" and set it to 0 then empty but that did not work.
any Ideas???
Thanks
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 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.
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
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 have a pivot chart with a bunch of data series, and every time we
include/exclude one of the series to see how the chart changes, all the
colors change and we have to spend time re-identifying which series is
which, using the legend to check the colors.
Is there a way to "lock" the colors, so that the bars stay the same colors
regardless of whether other bars are included/excluded?
Thanks!
Keith
include/exclude one of the series to see how the chart changes, all the
colors change and we have to spend time re-identifying which series is
which, using the legend to check the colors.
Is there a way to "lock" the colors, so that the bars stay the same colors
regardless of whether other bars are included/excluded?
Thanks!
Keith
Hi Professionals,
I have just tried running the following code
Code:
in the hope that it might 'unfilter' my table but it literally doesn't do a thing!
My table is called Activity_Table
Can you provide me with something that will 'unfilter' my table if it is filtered on any or all 6 columns?
Help appreciated,
Dominic
I have just tried running the following code
Code:
Dim ws As Worksheet For Each ws In ThisWorkbook.Sheets With ws If .FilterMode Then .ShowAllData End With Next ws
in the hope that it might 'unfilter' my table but it literally doesn't do a thing!
My table is called Activity_Table
Can you provide me with something that will 'unfilter' my table if it is filtered on any or all 6 columns?
Help appreciated,
Dominic
I automating a task at work that will save some time. Seems simple enough but for some reason the code I have to cause IE to wait unil ready doesnt seem to work in all cases.
Im using :
'wait until the page loads
Do While ie.Busy Or ie.readyState READYSTATE_COMPLETE
DoEvents
Loop
This doesnt seem to work well enough, since actions are occuring that should NOT occur until the browser is ready.
Any other ideas as to how to make IE wait until the page is completely ready? I wondered about possibly checking the .innerHTML to verify that a unique page element is present.
Thanks,
Scott
Im using :
'wait until the page loads
Do While ie.Busy Or ie.readyState READYSTATE_COMPLETE
DoEvents
Loop
This doesnt seem to work well enough, since actions are occuring that should NOT occur until the browser is ready.
Any other ideas as to how to make IE wait until the page is completely ready? I wondered about possibly checking the .innerHTML to verify that a unique page element is present.
Thanks,
Scott
I did a bit of browsing on this problem. Found others suffering the same but haven't found any conclusive answer yet.
Every so often when I attempt to save a file, (including save as), Excel won'r let me. By won't let me I mean:
using Save doesn't appear to do anything using Save As doesn't either do anything, the dialog is not displayed and if I am doing via the File menu then the File menu is exited and the previous ribbon tab is displayed (i.ethe one I was on before clicking 'File') if I close the workbook I am prompted to save, close without saving or cancel. Clicking save just invokes the same msgbox again. I can't work out when it goes into this mode. Some days I can work without this problem, other days I encounter this 2 or 3 times.
The only thing I could suspect was I think this started around about the time I installed xlDennis' code library. I have uninstalled the addin and so far so good, but I cannot categorically say that this was the cause.
Anyone have any idea?
Cheers
Jon
Edit: I have read this: http://support.microsoft.com/kb/271513
Doesn't seem to cover the issue I describe
Every so often when I attempt to save a file, (including save as), Excel won'r let me. By won't let me I mean:
using Save doesn't appear to do anything using Save As doesn't either do anything, the dialog is not displayed and if I am doing via the File menu then the File menu is exited and the previous ribbon tab is displayed (i.ethe one I was on before clicking 'File') if I close the workbook I am prompted to save, close without saving or cancel. Clicking save just invokes the same msgbox again. I can't work out when it goes into this mode. Some days I can work without this problem, other days I encounter this 2 or 3 times.
The only thing I could suspect was I think this started around about the time I installed xlDennis' code library. I have uninstalled the addin and so far so good, but I cannot categorically say that this was the cause.
Anyone have any idea?
Cheers
Jon
Edit: I have read this: http://support.microsoft.com/kb/271513
Doesn't seem to cover the issue I describe