Need help with a macro for data manipulation

  • Thread starter Thread starter jerry.ranch
  • Start date Start date
J

jerry.ranch

I get a data dump from a large cooporate database that I use for data
mining. I need to "massage" the information (get the dates in proper
formats, numbers as well, get rid of unwanted columns, dump records
with incomplete data etc)

Most of this cleanup I can do with a macro by recording keystrokes.

One thing I don't know how to do, is to write a macro (or part of the
macro) that deletes records where certain fields are null (or have
meaningless data, that I define). I do not know how many rows I'll
get out of the data dump, but the fields names (columns) are constant

Now I could easily bring this data into access, and do the
manipulations there with queries, but I'd like to see what I can
accomplish with excel as well, since I mainly use Excel for the mining
aspect (better pivot tables etc)

Feedback
Thanks
Jerry


Example


ID name result
1 Fred 1
2 Jerry 2
3 Fred 12
4 Marie
5 Ken 34


So I'd want to run a macro that deletes records where [Results] is
null
 
If you can pick out a column that always has data if that row is used, you could
use:

Dim LastRow as long
dim iRow as long
with activesheet
LastRow = .cells(.rows.count,"A").end(xlup).row
for irow = lastrow to 1 step - 1
if isempty(.cells(irow,"B").value) _
or lcase(.cells(irow,"C").value) = lcase("Deleteme") then
.rows(irow).delete
end if
next irow
end with

If you don't have that kind of column, you could rely on .specialcells:

LastRow = .cells.specialcells(xlcelltypelastcell).row

Which should work especially if you're importing a text file.




I get a data dump from a large cooporate database that I use for data
mining. I need to "massage" the information (get the dates in proper
formats, numbers as well, get rid of unwanted columns, dump records
with incomplete data etc)

Most of this cleanup I can do with a macro by recording keystrokes.

One thing I don't know how to do, is to write a macro (or part of the
macro) that deletes records where certain fields are null (or have
meaningless data, that I define). I do not know how many rows I'll
get out of the data dump, but the fields names (columns) are constant

Now I could easily bring this data into access, and do the
manipulations there with queries, but I'd like to see what I can
accomplish with excel as well, since I mainly use Excel for the mining
aspect (better pivot tables etc)

Feedback
Thanks
Jerry

Example

ID name result
1 Fred 1
2 Jerry 2
3 Fred 12
4 Marie
5 Ken 34

So I'd want to run a macro that deletes records where [Results] is
null
Click to expand...
 
Ps. I didn't notice the example at the bottom of your original message.


I get a data dump from a large cooporate database that I use for data
mining. I need to "massage" the information (get the dates in proper
formats, numbers as well, get rid of unwanted columns, dump records
with incomplete data etc)

Most of this cleanup I can do with a macro by recording keystrokes.

One thing I don't know how to do, is to write a macro (or part of the
macro) that deletes records where certain fields are null (or have
meaningless data, that I define). I do not know how many rows I'll
get out of the data dump, but the fields names (columns) are constant

Now I could easily bring this data into access, and do the
manipulations there with queries, but I'd like to see what I can
accomplish with excel as well, since I mainly use Excel for the mining
aspect (better pivot tables etc)

Feedback
Thanks
Jerry

Example

ID name result
1 Fred 1
2 Jerry 2
3 Fred 12
4 Marie
5 Ken 34

So I'd want to run a macro that deletes records where [Results] is
null
Click to expand...
 
Back
Top