Delete rows if cells have character(s)...

[elsoung]

Well, it's been a long time since I've work complex Excel editor, perhaps at five yrs, and I have to make a more readable Excel report of data pull out of MAS.

Total nightmare, 10,000 rows of that!



Anyways, to get start what's the formula, function, macro, vba for deleting rows if a cell have character(s)?

[rylo]

Hi

formula - isnumber(cell_reference)
VBA - isnumeric(cell_reference)

Now how you do what you want to do with that information really depends on how you are approaching things. Macro or manual based on formulas, filters etc.

rylo

[elsoung]

Don't see how the isnumber(cell_reference) formula deletes rows if a specify cell has at least one character.



I would like to delete rows 7 and 11. Is there anyway to delete that rows just because there are characters in cells I7 and I11 or P7 and P11?

[rylo]

Hi

Hard to tell from the screen shot (how about attaching an example file), but do you want to delete the row if there is anything in column I or P not just a non numeric value? It looks as though all the other cells in those columns are blank.

rylo

[elsoung]

Hi

Hard to tell from the screen shot (how about attaching an example file), but do you want to delete the row if there is anything in column I or P not just a non numeric value? It looks as though all the other cells in those columns are blank.

rylo

Column I or P don't have numeric characters, I'm just asking how you can delete rows if a cell has anything in it.

[elsoung]

One other question. in the attached example1.xlsx. I've inserted another column, colunm A. Is there anyway pull out only the 50-02s, 50-03s, 50-04s, etc... but not the stores like 50-B84116, 50-C05689, etc...

The 50-05 is the same saleman is doesn't matter about the 50-05c, 50-05f, etc... I just want to pull out the salesman and not the stores.

[rylo]

Hi

For both I think you can use filters to determine the relevant rows, then select the visible rows and delete them. Have you tried to do anything with filters with regard to this?

rylo

[elsoung]

No, haven't thought of using filters.

Good idea, it's just that I've been out of this Excel world for probably a good 7-10 yrs and suddenly my job as an IT System Tech has suddenly thrown this Sage MAS database-to-Excel of at least 50,000 rows at me and creating a spreadsheet report and be a financial analyst!

Got anymore ideas in filtering to bring back old memories to help get me rolling?


Thanks!

[rylo]

Hi

OK, run the following across your data. It will get rid of the relevant rows, remove the merged cells, and apply an autofilter.

Please Login or Register  to view this content.

Then select B1, select the filter dropdown, text filters, Begins with, 50-05, and OK. This will bring back all the required entries.

HTH

rylo