Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
delete rows
I've a spreadsheet of inventory information that was exported from
QuickBooks. The resulting spreadsheet contains a number of rows with no information in the column I want to sort on, so I would like to automatically delete those rows. I'm totally new to writing VBA for Excel, but I'm quite comfortable with it in Access, so understand it somewhat. I also understand I can modify the QuickBooks report so the spreadsheet is created in a format that will work, but I would like some assistance with the code so I can start to learn what I'm doing in Excel. Thanks, Ivor |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
delete rows
You will need to adjust to your file criteria.
Sub DelRw() lstRw = Cells(Rows.Count, 1).End(xlUp).Row For i = lstRw To 2 Step -1 'Assumes Header Row If Cells(i, 1) = "" Then 'Change col 1 to actual Cells(i, 1).EntireRow.Delete End If Next End Sub "Ivor Williams" wrote: I've a spreadsheet of inventory information that was exported from QuickBooks. The resulting spreadsheet contains a number of rows with no information in the column I want to sort on, so I would like to automatically delete those rows. I'm totally new to writing VBA for Excel, but I'm quite comfortable with it in Access, so understand it somewhat. I also understand I can modify the QuickBooks report so the spreadsheet is created in a format that will work, but I would like some assistance with the code so I can start to learn what I'm doing in Excel. Thanks, Ivor |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
delete rows
Thank you for the suggestion. Would you be kind enough to explain the code
so I understand what each line means. Ivor "JLGWhiz" wrote in message ... You will need to adjust to your file criteria. Sub DelRw() lstRw = Cells(Rows.Count, 1).End(xlUp).Row For i = lstRw To 2 Step -1 'Assumes Header Row If Cells(i, 1) = "" Then 'Change col 1 to actual Cells(i, 1).EntireRow.Delete End If Next End Sub "Ivor Williams" wrote: I've a spreadsheet of inventory information that was exported from QuickBooks. The resulting spreadsheet contains a number of rows with no information in the column I want to sort on, so I would like to automatically delete those rows. I'm totally new to writing VBA for Excel, but I'm quite comfortable with it in Access, so understand it somewhat. I also understand I can modify the QuickBooks report so the spreadsheet is created in a format that will work, but I would like some assistance with the code so I can start to learn what I'm doing in Excel. Thanks, Ivor |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
delete rows
Su
Sub DelRw() 'This is the title line which you can use when 'calling this macro. Just type DelRw in other code 'within the same workbook. lstRw = Cells(Rows.Count, 1).End(xlUp).Row 'This line sets the 'variable lstRw equal to the number of the last row 'containing data. For i = lstRw To 2 Step -1 'Assumes Header Row and establishes a 'beginning for a loop with a numerical limit. 'Since you are deleting entire rows, it starts 'at the bottom so no rows will be skipped due ' to the automatic upward shift after delete. If Cells(i, 1) = "" Then 'Change col 1 to actual and sets the 'criteria for deleting a row. In this case 'it starts with the last row with data in 'column A. Cells(i, 1).EntireRow.Delete 'Executes the delete action for the cell 'if it met the criteria, which in this case is 'if the cell = null string. End If 'Closes the if block Next 'Goes to nest item up on the active sheet and 'repeats the process until the For limits expire. End Sub 'Closes the procedure. "Ivor Williams" wrote: Thank you for the suggestion. Would you be kind enough to explain the code so I understand what each line means. Ivor "JLGWhiz" wrote in message ... You will need to adjust to your file criteria. Sub DelRw() lstRw = Cells(Rows.Count, 1).End(xlUp).Row For i = lstRw To 2 Step -1 'Assumes Header Row If Cells(i, 1) = "" Then 'Change col 1 to actual Cells(i, 1).EntireRow.Delete End If Next End Sub "Ivor Williams" wrote: I've a spreadsheet of inventory information that was exported from QuickBooks. The resulting spreadsheet contains a number of rows with no information in the column I want to sort on, so I would like to automatically delete those rows. I'm totally new to writing VBA for Excel, but I'm quite comfortable with it in Access, so understand it somewhat. I also understand I can modify the QuickBooks report so the spreadsheet is created in a format that will work, but I would like some assistance with the code so I can start to learn what I'm doing in Excel. Thanks, Ivor |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
delete rows
Hi Ivor
If you have a lot of rows (ie into the 1000s) then using autofilter will increase the spped of execution considerably over looping: Sub RemoveBlanks() Dim r As Range Set r = Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row) 'spec the range to run from A1 to the last row 'note that presumed header in row 1. Change "A" column to the actual column With r .AutoFilter field:=1, Criteria1:="=" 'filter range for blanks .Offset(1).EntireRow.Delete 'offset so don't delete header row (only visible cells will be deleted) .AutoFilter 'turn off autofilter End With End Sub Best regards Richard On Dec 29, 3:11 pm, "Ivor Williams" wrote: I've a spreadsheet of inventory information that was exported from QuickBooks. The resulting spreadsheet contains a number of rows with no information in the column I want to sort on, so I would like to automatically delete those rows. I'm totally new to writing VBA for Excel, but I'm quite comfortable with it in Access, so understand it somewhat. I also understand I can modify the QuickBooks report so the spreadsheet is created in a format that will work, but I would like some assistance with the code so I can start to learn what I'm doing in Excel. Thanks, Ivor |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
delete rows
I've tried the following and get an Error 400. If I step into the code, it
gets hung up on the second line. G is the actual column I want to check for null values. Sub RemoveBlanks() Dim r As Range Set r = Range("A1:A" & Cells(Rows.Count, "G").End(x1Up).Row) With r .AutoFilter field:=1, Criteria1:="=" .Offset(1).EntireRow.Delete .AutoFilter End With End Sub Thanks, Ivor "RichardSchollar" wrote in message ... Hi Ivor If you have a lot of rows (ie into the 1000s) then using autofilter will increase the spped of execution considerably over looping: Sub RemoveBlanks() Dim r As Range Set r = Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row) 'spec the range to run from A1 to the last row 'note that presumed header in row 1. Change "A" column to the actual column With r .AutoFilter field:=1, Criteria1:="=" 'filter range for blanks .Offset(1).EntireRow.Delete 'offset so don't delete header row (only visible cells will be deleted) .AutoFilter 'turn off autofilter End With End Sub Best regards Richard On Dec 29, 3:11 pm, "Ivor Williams" wrote: I've a spreadsheet of inventory information that was exported from QuickBooks. The resulting spreadsheet contains a number of rows with no information in the column I want to sort on, so I would like to automatically delete those rows. I'm totally new to writing VBA for Excel, but I'm quite comfortable with it in Access, so understand it somewhat. I also understand I can modify the QuickBooks report so the spreadsheet is created in a format that will work, but I would like some assistance with the code so I can start to learn what I'm doing in Excel. Thanks, Ivor |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Delete Rows if any cell in Column H is blank but do not Delete Fir | Excel Programming | |||
Copy pasting Rows, but need to Delete any Shapes/Pictures that are within copied rows | Excel Programming | |||
Delete rows with numeric values, leave rows with text | Excel Programming | |||
How to delete rows when List toolbar's "delete" isnt highlighted? | Excel Worksheet Functions | |||
Delete every 3rd row, then delete rows 2-7, move info f/every 2nd row up one to the end and delete the row below | Excel Programming |