We help IT Professionals succeed at work.

Excel macro to filter rows based on case sensitivity & font color

Andreamary
Andreamary asked
on
797 Views
Last Modified: 2014-08-27
I need to create a macro that filters a large spreadsheet for all rows that meet all of the following criteria:
Red font color only (exclude rows that mixed font colors, ie., red and black)
First word in string is in all capital letters
Row ends with a minimum of 5 spaces preceding a 4-character string in all capital letters that starts with "C"

Thanks!
Andrea
Comment
Watch Question

Glenn RayExcel VBA Developer
CERTIFIED EXPERT
Top Expert 2014

Commented:
Example data, please, or provide more-specific information on size of data table (ex., number of columns, examples of data that would pass the filter criteria).  

Also:  I presume in the second criteria you mean the first word in the first cell must be all caps.  If different, please clarify.
Same for third criteria; is this the last cell in the row?

Regards,
-Glenn

Author

Commented:
Hi Glenn,

I created a sample workbook. The first tab is sample data, and the second tab shows the results I want to extract from the data. If it's possible to include having the desired results copied into a blank worksheet as part of the solution to my question, that would be great!

Thanks,
Andrea
EE-SampleData-Results.xlsx
Excel VBA Developer
CERTIFIED EXPERT
Top Expert 2014
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
Hi Glenn,

Thanks so much!!

Unfortunately there is a weird thing that is happening, which is to do with my data since the sample data I sent is not a problem, and I'm not sure what is causing it:

When I ran the macro, it created the Results sheet but there were no results listed. Thinking that maybe the red font color in the values wasn't identical to the RGB values in the macro, I checked the first value and the RGB values were correct. When I ran the macro again, the single value that I double-clicked on was successfully transferred to the Results spreadsheet. I continued to experiment, and for every value I double-clicked on, that value was transferred across when re-running the macro. Any value I didn't double-click on was not transferred. So somehow the RGB values are not recognized until that row is double-clicked.

The reason my sample data spreadsheet didn't cause any issues is that I had to edit each row (for confidentiality reasons).

Background on the data, in case it sheds some light:
The original source of this data is a PDF that was opened in Acrobat Pro and saved as a Microsoft Excel spreadsheet.

Any thoughts on this strangeness? Or perhaps the best bet is to adjust the macro so it emulates the double-clicking action I am doing for all rows before it runs through the main event?

Thanks,
Andrea
Glenn RayExcel VBA Developer
CERTIFIED EXPERT
Top Expert 2014

Commented:
I have a macro that I run that "refreshes" large, selected ranges of data.  It essentially does something like you did - double-clicking and re-entering each cell (like [F2] and [Enter]).

Add this macro to your workbook (or better yet, your Personal Macro Workbook).  Then select all the data to update and run this macro.  THEN, run my previously-submitted macro.  Let me know if this works.

-Glenn

Sub ReEnter()
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Dim x, tCells As Long
    tCells = Selection.Count
    For x = 1 To tCells
        Selection.Item(x).Formula = Trim(Selection.Item(x).Formula)
    Next x
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
End Sub

Open in new window

Author

Commented:
Hi Glenn,

Unfortunately running the above macro doesn't seem to make a difference. I tried it a couple of times, and then went back again and manually double-clicked on a row that had one of the values, saved and ran the macro again, and that one value transferred over to the results sheet.

Interestingly, when I copied the spreadsheet into Microsoft OneNote, then re-pasted it back into Excel, the Move_Select_Data then worked. I noticed when the PDF is running through it's conversion into an Excel format, the progress bar at one point refers to OCR, which is why I tried the OneNote process as it has OCR capabilities. I don't know if that has anything to do with the issue here...curious.

Any other thoughts?

Andrea

Author

Commented:
Hi Glenn,

I'm very pleased...thanks so much!

Cheers,
Andrae
Glenn RayExcel VBA Developer
CERTIFIED EXPERT
Top Expert 2014

Commented:
I'm sorry I didn't get a chance to follow up on your issue with my "Re-Enter" macro.  It only works on a selected range of cells (i.e., you have to highlight/select them all before running the macro), but it still should have refreshed even a single cell like the red ones in your data.

I'm glad I was able to help some.

Regards,
-Glenn

Author

Commented:
Hi Glenn,

Thanks for your follow-up. I ran the "ReEnter" macro after selecting the range of cells as suggested, and unfortunately it removed the font formatting, including the red font so the "Move_Select_Data" macro doesn't work.

If it's possible to get the "ReEnter" macro to run and refresh the data without removing font color, that would be great. Since this thread is closed, should I be posting this as a new question?

Cheers,
Andrea
Glenn RayExcel VBA Developer
CERTIFIED EXPERT
Top Expert 2014

Commented:
No; since the issue you're having is a result of code I offered here, I think we should try and resolve it within this question.

Is it possible to attach a copy of the file with the PDF data - redacted, if necessary?  I'd like to see the behavior you're talking about and maybe provide another automated solution.

-Glenn

Author

Commented:
Hi Glenn,

Thanks for offering to pursue this.

I've created a sample Excel file as attached that demonstrates the behavior. In the results sheet, you'll notice there are two results — these results are coming from the rows in Table 1 which I double-clicked. The remaining rows that meet the criteria, row 7 and 10, did not transfer over. If I double-click them, however, then save the file and run the macro, they will be included in the results.

I'll be interested to hear if you find the same behavior occurs at your end, and if so, what your take is on this!

Cheers,
Andrea
TestPDFToExcel.xlsm
Glenn RayExcel VBA Developer
CERTIFIED EXPERT
Top Expert 2014

Commented:
Very interesting.  I can duplicate your scenario and see that although the cells appear red with an Arial font, the formatting underneath is different (Times New Roman, "Automatic" - black -  font color).

I've created a new updating subroutine ("F2_It") and if your select your data in column A and run this, it will actually execute the [F2] and [Enter] keystrokes on all cells in the range.  I tried to incorporate this code into the original subroutine ("Move_Select_Data") and was not able to get the code to work (still not sure why; the SendKeys command is tempermental in this regard).

So, going forward you need to do the following:
1) upload your PDF data into column A of the sheet
2) Select the data in column A and run "F2_It" macro on it to clean up.
3) Run "Move_Select_Data" macro to move.

The attached workbook has the updated code.

-Glenn
EE-TestPDFToExcel.xlsm

Author

Commented:
Hi Glenn,

Thanks very much for the new macro.

There is a line containing the following text that is causing the macro to hang up:

-H.  ·-

I don't know how often this particular string will recur (the PDF I will be converting  into Excel contains over 1000 pages).

Any thoughts on how best to deal with this in an automated way?

Thanks,
Andrea
Glenn RayExcel VBA Developer
CERTIFIED EXPERT
Top Expert 2014

Commented:
The reason the macro is hanging up on
-H.  ·-

is because it begins with a hyphen, or as Excel considers it, a minus sign.  As such, Excel is trying to determine the result of the formula and, of course, there is no formula here.  Normally, Excel wouldn't even allow the existence of a cell like this, but since you're getting the data from a PDF file, I guess all bets are off.

I tweaked the F2_It subroutine to change all cells to "Text" format from "General" so that this possibility will be overlooked.  The method is the same as before: Highlight the whole range in column A to update, run F2_It, then run Move_Select_Data.  Updated workbook attached.

-Glenn
EE-TestPDFToExcel.xlsm

Author

Commented:
Terrific, Glenn...works perfectly!! Thank you SO much for all your help!

Best,
Andrea
Glenn RayExcel VBA Developer
CERTIFIED EXPERT
Top Expert 2014

Commented:
You're Welcome.  This was an interesting challenge.  Just wish I'd been able to combine the two subroutines (still scratching my head on that part).

-Glenn

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.