Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 . |
#4
|
|||
|
|||
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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Extract specific data into its own workbook via macro? | Excel Discussion (Misc queries) | |||
External data Macro Problem Excel 97 | Excel Discussion (Misc queries) | |||
Macro does not run when data refreshed | Excel Discussion (Misc queries) | |||
Pulling data from 1 sheet to another | Excel Worksheet Functions | |||
Daily Macro to Download Data, Order and paste in order | Excel Worksheet Functions |