+ Reply to Thread
Results 1 to 4 of 4

Combining 2 codes?

Hybrid View

bjcowen9000 Combining 2 codes? 02-19-2013, 01:34 PM
AB33 Re: Combining 2 codes? 02-19-2013, 02:11 PM
bjcowen9000 Re: Combining 2 codes? 02-19-2013, 02:39 PM
bjcowen9000 Re: Combining 2 codes? 02-19-2013, 02:48 PM
  1. #1
    Forum Contributor
    Join Date
    02-13-2013
    Location
    Columbia, SC
    MS-Off Ver
    Excel 2010
    Posts
    217

    Combining 2 codes?

    Is there a way to combine these two codes so one runs right after the other?

    Sub RemoveDupe()
    Dim ws As Worksheet
    Dim rCell As Range
    Dim rRange As Range
    Dim lCount As Long
    For Each ws In Worksheets
    
    Set rRange = ws.Range("B1", ws.Cells(ws.Rows.Count, "B").End(xlUp))
    lCount = rRange.Rows.Count
    
    For lCount = lCount To 1 Step -1
    With rRange.Cells(lCount, 1)
    If WorksheetFunction.CountIf(rRange, .Value) > 1 Then
    .EntireRow.Delete
    End If
    End With
    Next lCount
    Next ws
    End Sub

    And

    Sub DeleteRowWithContents()
    
     Dim ws As Worksheet
       For Each ws In Worksheets
          With ws
              Last = .Cells(Rows.Count, "D").End(xlUp).Row
              For i = Last To 2 Step -1
                 If (.Cells(i, "D").Value) <> "01" Then
    
                 .Cells(i, "A").EntireRow.Delete
                 End If
              Next i
          End With
       Next ws
     End Sub

  2. #2
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Combining 2 codes?

    I have not tested it, but try it

    Sub RemoveDupe()
    Dim ws As Worksheet, rCell As Range, rRange As Range, lCount As Long
    
        For Each ws In Worksheets
    
            Set rRange = ws.Range("B1", ws.Cells(ws.Rows.Count, "B").End(xlUp))
            lCount = rRange.Rows.Count
    
            For lCount = lCount To 1 Step -1
            With rRange.Cells(lCount, 1)
            If WorksheetFunction.CountIf(rRange, .Value) > 1 Then
            .EntireRow.Delete
            End If
            End With
        Next lCount
                With ws
                
                    For i = .Cells(.Rows.Count, "D").End(xlUp).Row To 2 Step -1
                        If (.Cells(i, "D").Value) <> "01" Then .Rows(i).Delete
                    Next i
                End With
    Next ws
    End Sub

  3. #3
    Forum Contributor
    Join Date
    02-13-2013
    Location
    Columbia, SC
    MS-Off Ver
    Excel 2010
    Posts
    217

    Re: Combining 2 codes?

    Seems to work. Do you think there is any way to combine that with the other code you helped me with?
    I realize I keep asking but it is crazy to me that all of this can be combined. I had ten modules! I think the code you just did above needs to come last in the order. The other code was:

    Sub InsertColumns()
    
     Dim ws As Worksheet, LR&, i&, c As Range
     
      Application.ScreenUpdating = 0
      
     For Each ws In ThisWorkbook.Worksheets
     
        With ws
        
            On Error Resume Next
     
            .Cells.Find("Item #").EntireColumn.Offset(0, 1).Insert
    
            .Cells.Find("Item #").EntireColumn.Offset(0, 1).Insert
    
            .Range("C1").Value = "Item #"
     
            .Range("D1").Value = "Class #"
     
            .Range("B:B").Copy .Range("C:C")
            
                 LR = .Cells.Find("*", , , , xlByRows, xlPrevious).Row
            
                 For i = 2 To LR
                    .Cells(i, 4) = "'" & Left(.Cells(i, 2), 2)
                 Next i
                 
                        For Each c In .Range("C2:C" & LR)
                        c.Value = c.Value
                        Next
        End With
        With ws
             Set Rng = .Range("F2", .Range("F" & Rows.Count).End(xlUp))
         End With
            Rng.TextToColumns Destination:=ws.Range("X2"), DataType:=xlDelimited, _
            TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
            Semicolon:=False, Comma:=False, Space:=True, Other:=False, OtherChar _
            :="-", FieldInfo:=Array(Array(1, 2), Array(2, 2), Array(3, 2), Array(4, 2), Array(5, 2), _
            Array(6, 2), Array(7, 2), Array(8, 2)), TrailingMinusNumbers:=True
            
                Dim s As Worksheet
        For Each s In Sheets
               'hide required columns
                s.Columns("G:W").Hidden = True
                       Next
    
     
     Next ws
      Application.ScreenUpdating = 1
     End Sub

  4. #4
    Forum Contributor
    Join Date
    02-13-2013
    Location
    Columbia, SC
    MS-Off Ver
    Excel 2010
    Posts
    217

    Re: Combining 2 codes?

    It works! I figured out combining other stuff too!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1