| 0 |
Subject: Excel Problem
Posted by: allhair allstars
- Sustainer [50902421] Thu, Mar 24, 2005, 23:29
I could use a little help with this one...
I have a data table with a set number of columns, but the number of rows will change from time to time. Eventually I'll be downloading info and uploading the resulting table automatically, and I won't be able to manually tweak the data.
The problem is this: I want to be able to automatically delete garbage rows at the end of my table. Say I have 500+/- rows of usable data, and 20+/- rows of excess text, etc. I want a macro that will search for the end of the 500 rows and then delete, say the next 100 rows.
The best way I've found to be able to differentiate between the real data and the garbage text is that one column (C) does not have any other data in it other than real data. In other words, the first 500+/- rows (in the column) are full of good data, and then there is nothing else bad following.
Using this column, I set up a row count, and was thinking I could use that count to somehow delete the rows after the real data portion ended, but after a fair bit of noodling I've had no luck.
Any suggestions would be muchly appreciated. There might be a simple solution that I'm just not seeing, and I'd love to find out what it is... |
| 1 | Richard
Dude
ID: 204252420
Thu, Mar 24, 2005, 23:49
|
AA - if it were me, I'd create a cute little macro along these lines
Sub ClearRows()
'
' Macro created 3/24/2005 by Richard Cullip
'
'
'Find Last Row in Col B with good data
Last_Good_Row = WorksheetFunction.Count(Range("B:B")) + 2
'Clear Rows below Last_Good_Row
Range("A" & Last_Good_Row & ":B10000").Select
Selection.ClearContents
End Sub
Richard
|
| 2 | Trip
Sustainer
ID: 13961611
Fri, Mar 25, 2005, 00:02
|
If I had a similar problem, I would use MS access to convert the data. If you are dealing with a large number of records, it could speed up your data processing as well, especially when it comes to updating records. I'm still not exactly sure how I would process this, but if you could send me a couple of example tables, I may be able to get you started. If you don't have Access, I am not the person to help you out with this problem.
I would, of course, require a couple of random song reviews from your top 500.
|
| 3 | RecycledSpinalFluid
Dude
ID: 204401122
Fri, Mar 25, 2005, 00:50
|
Could you clear up this section for me:"The best way I've found to be able to differentiate between the real data and the garbage text is that one column (C) does not have any other data in it other than real data. In other words, the first 500+/- rows (in the column) are full of good data, and then there is nothing else bad following."
|
| 4 | allhair allstars
Sustainer
ID: 50902421
Fri, Mar 25, 2005, 20:09
|
Thanks all!
Sir Richard,
Gave your suggestion a quick try this morning pre-work but it didn't work. Instead of starting at the end of the data, it deleted everything from cell A1 and down...
Trip,
I have Access, but I'm trying to keep it all in spreadsheet form at this point. I'm running into a couple other problems (damn that Excel 2K, give me Excel97!) as well now so that wish might end up going out the window... And I'm working on a few other entries in the "occasional" series... Thanks for mentioning it! :)
RSF,
Like so...
RRRRRRR
RRRRRRR
RRRRRRR
RRRRRRR
RR_RRRR
RR_RRRR
The data I want ends at the 4th row, and the garbage continues a bit after that. The third column is empty, so I was trying to perform a count on that column, and use that # to delete the offending rows. Richard's suggestion would work perfectly if it worked at all... I'll have to spend a little more time with it...
Thanks for the suggestions... and any others that come to mind!
|
| 5 | Richard
Dude
ID: 204252420
Fri, Mar 25, 2005, 21:45
|
AA - does the column you are trying to count hold numbers or text? If I recall, the WorksheetFunction.Count only counts the cells that holds numbers. You could try the WorksheetFunction.CountA if you need to count text rows.
Richard
|
| 6 | allhair allstars
Sustainer
ID: 50902421
Fri, Mar 25, 2005, 21:52
|
Bingo!! Works like a charm!
One less problem to deal with... and now on to my next headache...
Richard, my thanks.
|
| 7 | Richard
Dude
ID: 204252420
Fri, Mar 25, 2005, 23:45
|
Always glad to help, AA. I'm glad you could adapt my little macro to the specifics of your spreadsheet.
Richard
|
|
|
|
Post a reply to this message: (But first, how about checking out this sponsor?)
|
|