deleting rows without certain values

[rinxman]

I copied data from a text file and built a text-to-columns macro to get the data into proper columns. The data comes with a lot of text that isn't needed and takes up about 500 rows. How can I delete all rows that do not contain data from a certain list? All of the data labels to reference are in column B after the text-to-columns (with percentages in column C).

 

[Mark F]

Could you give a bit more detail and make clearly exactly what you are hoping to achieve, including sample data, and what the end result would look like

Mark

 

[GTO]

...How can I delete all rows that do not contain data from a certain list? All of the data labels to reference are in column B after the text-to-columns (with percentages in column C).

I may be totally misunderstanding what you are after, but if you are saying that in column B, on ea row, the name of the list where that row's data is from is listed... And, that you only need to keep the rows that came from one specific list, then...

In a copy of your workbook, try:

Sub DeleteChaffe()
Dim wks As Worksheet
Dim lngLastRow As Long
Dim lngCount As Long
'//                         change to the string you want//
Const LIST_NAME As String = "mylabel"
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    '// sub the actual sheet you want this to run on.//
    Set wks = ActiveSheet
    lngLastRow = wks.Cells(Rows.Count, 2).End(xlUp).Row
    
    For lngCount = lngLastRow To 2 Step -1
        If Not wks.Cells(lngCount, 2).Value = LIST_NAME Then
            wks.Cells(lngCount, 2).EntireRow.Delete
        End If
    Next
        
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    
End Sub

Hope this helps,

Mark

 

[rinxman]

Mark, I am taking data from a report run by a scheduling application. I'm getting a total number of hours for each scheduled activity for each day of the next week. After a text-to-columns, the data labels will end up in columns B with the hours in column C. I can change this if need be. All of the options for scheduled activity will not appear, only those that are scheduled that particular day. So it might look something like this:
Date: 03/30/09
Break 222:00
Lunch 264:15
Meeting 14:00
Open Time 3198:30
Vacation 190:00

I have a list of all of the types of scheduled activity that might be scheduled. The report has a lot of extra text and data that isn't needed such as page breaks. There will be a lot of blank lines. I'm trying to clean up the report so it can be easily copied to another report.

 

[rinxman]

GTO, your suggestion deleted everything I needed to keep and kept everything I was trying to delete.

How do I have it do just the opposite?

 

[GTO]

I copied data from a text file and built a text-to-columns macro to get the data into proper columns. The data comes with a lot of text that isn't needed and takes up about 500 rows. How can I delete all rows that do not contain data from a certain list? All of the data labels to reference are in column B after the text-to-columns (with percentages in column C).

GTO, your suggestion deleted everything I needed to keep and kept everything I was trying to delete.

How do I have it do just the opposite?

Sorry I misunderstood, I took 'not...from a certain list' to be a 'label', and if its not a certain label, delete the row. If its the opposite - that is to say that we should keep only rows that have a certain label in Col B, then (again in a copy, lest we goober up your work) simply remove the Not in the IF test.

In a copy of your workbook, try:

Sub DeleteChaffe()
Dim wks As Worksheet
Dim lngLastRow As Long
Dim lngCount As Long
'//                         change to the string you want//
Const LIST_NAME As String = "mylabel"
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    '// sub the actual sheet you want this to run on.//
    Set wks = ActiveSheet
    lngLastRow = wks.Cells(Rows.Count, 2).End(xlUp).Row
 
    For lngCount = lngLastRow To 2 Step -1
        If Not wks.Cells(lngCount, 2).Value = LIST_NAME Then
            wks.Cells(lngCount, 2).EntireRow.Delete
        End If
    Next
 
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
 
End Sub

Does that work better?

Mark, errrr, I guess I'd better go w/GTO on this one (Greetings Mark F)

 

[rinxman]

Don't worry, GTO, this sheet is a work in progress. We already run the report a much slower way. I'm just working on enhancing it and got stuck at this part.

The change did work though, so thank you! There are a couple of tweaks I need to do to get rid of some other information, but I know how to take care of that. The important piece of this got resolved. Thanks again.