Search results

Thanks Much!

I have an Excel table that has records with a created date and time column. I want to use Advanced Filter to create a second table (and put it on another sheet)-- and this second table will only contain records from the first table that have a created date and time that is greater than 4pm the...

Thanks for all the ideas everybody! I may be adding pivot tables, so I will look to clear out pivot cache. I will also keep a backup of the file in its smallest state to reuse periodically.

I have a workbook with one worksheet and a bunch of macros. I pull in a range of data from another sheet and work with it-- it can have hundreds of rows, columns out to column BE. I do a lot of conditional formatting of the data via the macros. I've been noticing that over time, the file gets...

One glitch on this: Pearson's notes and his functions work great if your conditional formatting is simple (doesn't use a formula). Mine does. Pearson says to correct this, you have to use absolute cell references in your condition formula. I did, and then my formula didn't work. I had to...

The Pearson link you sent has some very valuable code in it. I can use his function to find sum of cells in a row that have CF, put the sum in a helper column, and then filter out all the zero sums of that helper column to just show me the error rows. Great! Thanks for your help Marc.

What do you put in the Criteria Range? There are multiple criteria, different for each column. And in this case to make it easy, I used "ABC" for Criteria 1, "TTT" for Criteria 7 and so on. On my actual sheet (which is WAY too large to upload), its more complex because it compares each cell...

My worksheet is very large so I created a smaller dummy version to attach that works basically the same way. I have a range with 12 rows-- with row 1 being a header row. There are 9 columns, one for a persons name and then Criteria-1 through Criteria 8. I run conditional formatting -- 3...

Luke: UPDATE! I got your code to work, with a couple of tweaks. First, I used a different method to get the LastRow, which keyed off column C (the first row the macro should care about, since columns A and B are different), and I changed the Reference to Range(Cells(2,x) to Range(Cells(8,x)...

Luke-- I ran your code on my spreadsheet, and it did not work. It should have hidden columns AW and AV and they are still there. I tried the ISBLANK formula for each of the cells =ISBLANK(AW8) through =ISBLANK(AW14) as you suggested and they all came out TRUE. One thing that is quirky about...

I had earlier code on this but could not get it to work. This is new code that is supposed to work, but still has a glitch somewhere. What the HideLabelColumns macro is supposed to do is look at a part of my range (only columns I through AW) and it should examine them from right to left-- if...

I'm uploading my file. I'm focusing on the Labels columns-- Labels-1 through Labels-39. My actual table is C7:AW15 (columns A and B are for something else). Today, we have only 7 rows of data in the table (it could be different tomorrow)-- that's why I'm using lrow variable. The macro (Pink...

I coded this up (used a different expression to ask if chk_yx isn't empty), but it does nothing. Do I have my variables mixed up? Here's my code: Sub HideEmptyColumns() Application.ScreenUpdating = False Dim lRow As Long lRow = ActiveSheet.Cells(Rows.Count, "C").End(xlUp).Row...

This macro works great for this specific worksheet. However, I have two questions that I need to figure out if I'm going to be able to apply your code to another sheet. On this other sheet, I need to run the macro each day, and each day the number of rows is going to vary. I think I can run...

Sorry-- it would not DELETE columns, only HIDE them. The columns to be hidden would be the ones that are tested beginning with the far right column and that prove to have no data in the cells in the grid. Your macro is PERFECT! It does exactly what I was looking for. You saved me a lot of...

I have a range (with 5 rows of data (and a top row for column headers). Column A is just a person's first name, and the remaining columns are labeled A through F. The cells in the grid will have a simple YES or NO, but not every cell is filled in for each person-- at some point, the cells will...

Ok, thanks. I guess this is a flaw in Excel. The people I'm giving this sheet to probably won't want to save and close and then re-open to see the real results, but I don't see any other way to do this programmatically. Their choice will be to do the save-close-reopen, or create the...

I ran your new code and it still conditionally formats every cell. Also, I have a separate macro that clears out all the formatting on the whole sheet and I've been running that too-- it doesn't seem to help.

Ok-- I subsequently sent you a workbook with something a lot smaller but it does the same thing. I'm wondering if this is a flaw in Excel.

I pared down my code so I could attach. On this simplified sheet, if I create a conditional formatting rule to check what's in column G against the allowable values in column C, it works fine. But when I create a macro that builds that same rule,using the exact same formula which is...

Actually, the range I'm testing against is B8:B64, so that part in the code is correct.

Ok, I'm unable to attach my workbook-- it is too large. On my worksheet, there are a row of buttons at the top of the worksheet to run the macros. I've already run macros to do the adding of blank columns and splitting the labels. The next set of buttons run macros that do some conditional...

If I set up some conditional formatting and test it out in my spreadsheet and it works fine, can I expect it to work if I use the macro recorder and record a macro that creates the same conditional formatting, reset everything (delete all conditional formatting on the sheet) and then run the...

Sorry-- forgot to post the file

Hui-- if you create a macro that sets up the conditional formatting, should it work? Attached is my workbook-- now it is macro enabled. Right now the macro has not been run-- instead, I use the conditional formatting that you helped me fix-- it works as it is supposed to right now. But if...