Help on a Macro

J

JCO

I have an Excel sheet with lots of data. The columns go from "A" to "R".
I have 2-4 thousand Rows to search through. What I would like is a Macro
that will delete all Rows that has the data "Unit(s)" located in Column "R".
How is this done. This is a report that is generated every month. This
would be a great help to have a macro that will eliminate all Rows that
displays the text "Unit(s)" in Column R.

Thanks
 
J

Jim Cone

Something similar to this will work.
You must select the portion of the column with the data before running the code.
If you select the entire column you will need to pack a lunch.
'--
Sub DeleteSpecificRows()
Dim N As Long
Dim R As Long
N = Selection.Columns(1).Cells.Count
For R = N To 1 Step -1
If Selection.Columns(1).Cells(R).Value = "Unit(s)" Then
Selection.Columns(1).Cells(R).EntireRow.Delete
End If
Next
End Sub
'--

See... http://tinyurl.com/XLCompanion for my commercial alternative.

--
Jim Cone
Portland, Oregon USA
http://www.mediafire.com/PrimitiveSoftware






"JCO" <[email protected]>
wrote in message
I have an Excel sheet with lots of data. The columns go from "A" to "R".
I have 2-4 thousand Rows to search through. What I would like is a Macro
that will delete all Rows that has the data "Unit(s)" located in Column "R".
How is this done. This is a report that is generated every month. This
would be a great help to have a macro that will eliminate all Rows that
displays the text "Unit(s)" in Column R.

Thanks
 
J

JCO

Just ran it. Your right about packing a lunch. I'm not sure I understood
what you mean about "select the portion of the column with the data....". I
selected the entire column. It works fine other than it takes awhile.
 
J

Jim Cone

The code looks at each cell in the first column of the selection.
If you select 4000 cells (just the data) instead of 65000 cells (entire column), the code should run in about 1/16 of
the time required for 65000 cells.



"JCO" <[email protected]>
wrote in message
Just ran it. Your right about packing a lunch. I'm not sure I understood
what you mean about "select the portion of the column with the data....". I
selected the entire column. It works fine other than it takes awhile.




Jim Cone said:
Something similar to this will work.
You must select the portion of the column with the data before running the
code.
If you select the entire column you will need to pack a lunch.
'--
Sub DeleteSpecificRows()
Dim N As Long
Dim R As Long
N = Selection.Columns(1).Cells.Count
For R = N To 1 Step -1
If Selection.Columns(1).Cells(R).Value = "Unit(s)" Then
Selection.Columns(1).Cells(R).EntireRow.Delete
End If
Next
End Sub
'--
Click to expand...
 
J

JCO

Okay then I did it correctly. I just selected the column.
Can I ask you another question?

I have a sort routine but when I run it on another file (similar data) I get
an error. This code was generated by using the Recorder. I selected the
whole data by taping the upper right corner (just left of the "A" Column).
I'm wondering if that is part of the issue. This sort does a double sort.
It needs to select the entire data (always a different size report
everyday). Sort on column "Assistance Category" (column P). Then with in
that it needs to Sort on Column "Last Name" (column C). I do need to check
the box that tells the sort that my data contains header so as to eliminate
the first row from the sort.

Cells.Select
ActiveWorkbook.Worksheets("NEEDWest_Errors_2010-Jan-Jun7").Sort.SortFields.
_
Clear
ActiveWorkbook.Worksheets("NEEDWest_Errors_2010-Jan-Jun7").Sort.SortFields.Add
_
Key:=Range("G2:G3869"), SortOn:=xlSortOnValues, Order:=xlAscending,
_
DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("NEEDWest_Errors_2010-Jan-Jun7").Sort.SortFields.Add
_
Key:=Range("B2:B3869"), SortOn:=xlSortOnValues, Order:=xlAscending,
_
DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("NEEDWest_Errors_2010-Jan-Jun7").Sort
.SetRange Range("A1:I3869")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

As you can see, the actual filename is embedded in the code and I'm
wondering if that is causing the issue too.
 
J

Jim Cone

First a recommendation...
When asking for Excel help, specify the Excel version, the Operating System and
if you are referencing an error, provide the error number. error description and
the code line on which it occurs.

The answer to your question is yes. If the sheet name doesn't exist in the workbook
Excel will throw an error. If the layout of the sheets is similar then substituting
"ActiveSheet" for specific sheet names should work. Give the following a try...
(Note that it sort on columns P and columns C)

'--
Sub PossibleSortCode()
Dim rngSort As Excel.Range
Set rngSort = ActiveSheet.Range("A1", ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell))

rngSort.Sort key1:=ActiveSheet.Columns("P:p"), order1:=xlAscending, _
key2:=ActiveSheet.Columns("C:C"), order2:=xlAscending, _
Header:=xlYes, MatchCase:=False, Orientation:=xlTopToBottom
Set rngSort = Nothing
End Sub
--
Jim Cone
Portland, Oregon USA
http://www.contextures.com/excel-sort-addin.html
(worth a look)






"JCO" <[email protected]>
wrote in message
Okay then I did it correctly. I just selected the column.
Can I ask you another question?

I have a sort routine but when I run it on another file (similar data) I get
an error. This code was generated by using the Recorder. I selected the
whole data by taping the upper right corner (just left of the "A" Column).
I'm wondering if that is part of the issue. This sort does a double sort.
It needs to select the entire data (always a different size report
everyday). Sort on column "Assistance Category" (column P). Then with in
that it needs to Sort on Column "Last Name" (column C). I do need to check
the box that tells the sort that my data contains header so as to eliminate
the first row from the sort.

Cells.Select
ActiveWorkbook.Worksheets("NEEDWest_Errors_2010-Jan-Jun7").Sort.SortFields.
_
Clear
ActiveWorkbook.Worksheets("NEEDWest_Errors_2010-Jan-Jun7").Sort.SortFields.Add
_
Key:=Range("G2:G3869"), SortOn:=xlSortOnValues, Order:=xlAscending,
_
DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("NEEDWest_Errors_2010-Jan-Jun7").Sort.SortFields.Add
_
Key:=Range("B2:B3869"), SortOn:=xlSortOnValues, Order:=xlAscending,
_
DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("NEEDWest_Errors_2010-Jan-Jun7").Sort
.SetRange Range("A1:I3869")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

As you can see, the actual filename is embedded in the code and I'm
wondering if that is causing the issue too.
 
J

JCO

Very sorry I did not give you enough info.
I'm using Office 2007 and running Windows XP Pro with all updates

I make the change to "ActiveSheet" and it seems to work but I need to test
it on multiple reports before I know for sure. Also, I looked at your code
and for some reason it works sometimes and other times it throws an error.
Next time it errors, I will let you know what that error is (sorry).

I really appreciate all you've done. Thanks so much and I hope this
newsgroup remains available unlike all the others that seem to be taken away
(I like using Windows Live Mail instead of a browser).

Thanks again
 
J

Jim Cone

You are welcome.

Re: "I like using Windows Live Mail instead of a browser"
I agree, in fact any newsreader is better than the web interface MS provides.
See... http://blog.contextures.com/archives/2010/06/30/the-excel-newsgroups-disappeared/
for information and options.

Re: "it works sometimes and other times it throws an error"
A couple of possible reasons: the sheet is protected; the data area does not extend out to column P.
--
Jim Cone
Portland, Oregon USA
http://tinyurl.com/ExtrasForXL





"JCO" <[email protected]>
wrote in message Very sorry I did not give you enough info.
I'm using Office 2007 and running Windows XP Pro with all updates

I make the change to "ActiveSheet" and it seems to work but I need to test
it on multiple reports before I know for sure. Also, I looked at your code
and for some reason it works sometimes and other times it throws an error.
Next time it errors, I will let you know what that error is (sorry).

I really appreciate all you've done. Thanks so much and I hope this
newsgroup remains available unlike all the others that seem to be taken away
(I like using Windows Live Mail instead of a browser).
Thanks again
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Macro Loop 0
Need Help with Macro Security 1
Setting column width in a table with a macro is not working 0
Macro to Select text from a point given to a specific word 2
Making a macro only work on certain files 2
Creating a Chart on form from selected data in query. 0
Need Help with a VBA subroutine 0
Mail Merge Help 1

Top