Deleting excess data in a macro
 

Hi,

I am downloading a report from Quickbooks that gives me
transaction details such as Customer Name, Date, Item
Number, Item Name, QTY and Total. Quickbooks then
provides a subtotal for each customer. In this row, all
of the other data in blank (there is not name, date, item
number etc in this row. What I want to do is to create a
macro that only has the transaction data in it and strips
out the subtotals. What I have done is to sort the
workbook by data which give me what I want. However, at
the end of the spreadsheet is all of the subtotals by
themselves with the blank data in all of the other
columns. I would like to delete these somehow. The
problem is that there is no set row that they start on.
One day it will be row 3465 and the next it will be
3531. I then plan to import this into Access but I don't
want the subtotal data to be imported and I can't figure
out how to filter it out during the import. Can anyone
help?

Thanks,

Chuck

Chuck,

Finding the last row is simple

cLastRow = Cells(Rows.Count,"A").End(xlUp).Row

To delete the rows, you need some identifying criteria, and then delete from
the bottom up, like so

For i = cLastRow to 1 Step -1
If Cells(i,"A").Value = "criteria" Then
Cells(i,"A").EntireRow.Delete
End If
Next i

This technique works without the data being sorted. B y being sorted, you
might be able to stop earlier.


--

HTH

RP
(remove nothere from the email address if mailing direct)


"ChuckW" wrote in message
...
Hi,

I am downloading a report from Quickbooks that gives me
transaction details such as Customer Name, Date, Item
Number, Item Name, QTY and Total. Quickbooks then
provides a subtotal for each customer. In this row, all
of the other data in blank (there is not name, date, item
number etc in this row. What I want to do is to create a
macro that only has the transaction data in it and strips
out the subtotals. What I have done is to sort the
workbook by data which give me what I want. However, at
the end of the spreadsheet is all of the subtotals by
themselves with the blank data in all of the other
columns. I would like to delete these somehow. The
problem is that there is no set row that they start on.
One day it will be row 3465 and the next it will be
3531. I then plan to import this into Access but I don't
want the subtotal data to be imported and I can't figure
out how to filter it out during the import. Can anyone
help?

Thanks,

Chuck

Bob,

Thanks for your help. Is this VB code that you are
giving me. I don't know too much about VB. Should I
somehow insert it into my macro?

Thanks,

Chuck

-----Original Message-----
Chuck,

Finding the last row is simple

cLastRow = Cells(Rows.Count,"A").End(xlUp).Row

To delete the rows, you need some identifying criteria,
and then delete from
the bottom up, like so

For i = cLastRow to 1 Step -1
If Cells(i,"A").Value = "criteria" Then
Cells(i,"A").EntireRow.Delete
End If
Next i

This technique works without the data being sorted. B y
being sorted, you
might be able to stop earlier.


--

HTH

RP
(remove nothere from the email address if mailing direct)


"ChuckW" wrote in
message
...
Hi,

I am downloading a report from Quickbooks that gives me
transaction details such as Customer Name, Date, Item
Number, Item Name, QTY and Total. Quickbooks then
provides a subtotal for each customer. In this row,
all
of the other data in blank (there is not name, date,
item
number etc in this row. What I want to do is to
create a
macro that only has the transaction data in it and
strips
out the subtotals. What I have done is to sort the
workbook by data which give me what I want. However,
at
the end of the spreadsheet is all of the subtotals by
themselves with the blank data in all of the other
columns. I would like to delete these somehow. The
problem is that there is no set row that they start on.
One day it will be row 3465 and the next it will be
3531. I then plan to import this into Access but I
don't
want the subtotal data to be imported and I can't
figure
out how to filter it out during the import. Can anyone
help?

Thanks,

Chuck


.

Chuck,

Yes it is VBA. If you aren't to clear, show us the code you already have and
we'll look at it.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"ChuckW" wrote in message
...
Bob,

Thanks for your help. Is this VB code that you are
giving me. I don't know too much about VB. Should I
somehow insert it into my macro?

Thanks,

Chuck

-----Original Message-----
Chuck,

Finding the last row is simple

cLastRow = Cells(Rows.Count,"A").End(xlUp).Row

To delete the rows, you need some identifying criteria,
and then delete from
the bottom up, like so

For i = cLastRow to 1 Step -1
If Cells(i,"A").Value = "criteria" Then
Cells(i,"A").EntireRow.Delete
End If
Next i

This technique works without the data being sorted. B y
being sorted, you
might be able to stop earlier.


--

HTH

RP
(remove nothere from the email address if mailing direct)


"ChuckW" wrote in
message
...
Hi,

I am downloading a report from Quickbooks that gives me
transaction details such as Customer Name, Date, Item
Number, Item Name, QTY and Total. Quickbooks then
provides a subtotal for each customer. In this row,
all
of the other data in blank (there is not name, date,
item
number etc in this row. What I want to do is to
create a
macro that only has the transaction data in it and
strips
out the subtotals. What I have done is to sort the
workbook by data which give me what I want. However,
at
the end of the spreadsheet is all of the subtotals by
themselves with the blank data in all of the other
columns. I would like to delete these somehow. The
problem is that there is no set row that they start on.
One day it will be row 3465 and the next it will be
3531. I then plan to import this into Access but I
don't
want the subtotal data to be imported and I can't
figure
out how to filter it out during the import. Can anyone
help?

Thanks,

Chuck


.