+ Reply to Thread
Results 1 to 10 of 10

Help! combining 2 VBA codes

Hybrid View

  1. #1
    Registered User
    Join Date
    11-08-2011
    Location
    KSA
    MS-Off Ver
    Excel 2007
    Posts
    93

    Help! combining 2 VBA codes

    How do I combined these 2 codes into one VBA code?

    The first code is a recorded macro

    Sub Macro1()
    '
    ' Macro1 Macro
    '
    
    '
        Rows("1:1").Select
        Selection.Delete Shift:=xlUp
        Columns("B:B").Select
        Selection.Delete Shift:=xlToLeft
        Columns("C:D").Select
        Selection.Delete Shift:=xlToLeft
        Columns("D:E").Select
        Selection.Delete Shift:=xlToLeft
        Columns("A:A").Select
        Selection.SpecialCells(xlCellTypeBlanks).Select
        Selection.FormulaR1C1 = "=R[-1]C"
        Columns("A:A").Select
        Selection.Copy
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Columns("B:B").Select
        Selection.SpecialCells(xlCellTypeBlanks).Select
        Application.CutCopyMode = False
        Selection.FormulaR1C1 = "=R[-1]C"
        Columns("B:B").Select
        Selection.Copy
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
    End Sub
    The second code is

    Sub deleteBlank()
        Dim Lastrow As Integer
        
        Lastrow = Range("B" & Rows.Count).End(xlUp).Row
        
        Range("C2:BC" & Lastrow).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
    End Sub
    Last edited by iamreese; 03-29-2012 at 08:26 AM.

  2. #2
    Forum Expert
    Join Date
    09-27-2011
    Location
    Poland
    MS-Off Ver
    Excel 2007
    Posts
    1,312

    Re: Help! combining 2 VBA codes

    maybe You can use call method
    Sub Macro1()
    '
    ' Macro1 Macro
    '
    
    '
        Rows("1:1").Select
        Selection.Delete Shift:=xlUp
        Columns("B:B").Select
        Selection.Delete Shift:=xlToLeft
        Columns("C:D").Select
        Selection.Delete Shift:=xlToLeft
        Columns("D:E").Select
        Selection.Delete Shift:=xlToLeft
        Columns("A:A").Select
        Selection.SpecialCells(xlCellTypeBlanks).Select
        Selection.FormulaR1C1 = "=R[-1]C"
        Columns("A:A").Select
        Selection.Copy
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Columns("B:B").Select
        Selection.SpecialCells(xlCellTypeBlanks).Select
        Application.CutCopyMode = False
        Selection.FormulaR1C1 = "=R[-1]C"
        Columns("B:B").Select
        Selection.Copy
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
            Call deleteBlank
    End Sub
    Sub deleteBlank()
        Dim Lastrow As Integer
        
        Lastrow = Range("B" & Rows.Count).End(xlUp).Row
        
        Range("C2:BC" & Lastrow).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
    End Sub
    Regards

    tom1977

    If You are satisfied with my solution click the small star icon on the left to say thanks.

  3. #3
    Registered User
    Join Date
    11-08-2011
    Location
    KSA
    MS-Off Ver
    Excel 2007
    Posts
    93

    Re: Help! combining 2 VBA codes

    Hi..thanks for the quick reply..

    I tried running the code you suggested and it did run correctly except for the third row where it did not delete the empty cells and gave me a debug error message

  4. #4
    Forum Expert
    Join Date
    09-27-2011
    Location
    Poland
    MS-Off Ver
    Excel 2007
    Posts
    1,312

    Re: Help! combining 2 VBA codes

    Hi
    Could You send the sample file with description what You want achieve

  5. #5
    Registered User
    Join Date
    11-08-2011
    Location
    KSA
    MS-Off Ver
    Excel 2007
    Posts
    93

    Re: Help! combining 2 VBA codes

    attached are 2 excel files.

    start.xlxs refers to the original file before applying the code

    result.xlxs will be the end result after applying the code. I had to run 2 codes separately.
    Attached Files Attached Files
    Last edited by iamreese; 03-29-2012 at 04:42 AM.

  6. #6
    Forum Expert
    Join Date
    09-27-2011
    Location
    Poland
    MS-Off Ver
    Excel 2007
    Posts
    1,312

    Re: Help! combining 2 VBA codes

    try to use this
    If You have a lot of data it could take a moment
    Sub aa()
    Dim Lastrow As Long
    Dim c As Long
    Dim x As Range
    
    Columns("G:H").Delete
    Columns("D:E").Delete
    Columns("B").Delete
        Lastrow = Range("C" & Rows.Count).End(xlUp).Row
        For c = Lastrow To 1 Step -1
        If IsEmpty(Cells(c, 3)) And IsEmpty(Cells(c, 3).Offset(0, -1)) Then
        Cells(c, 3).EntireRow.Delete
        End If
        Next
        Range("c2").Delete
        Call emptyrow
        For Each x In Range("c3:c" & Lastrow)
        If Not IsEmpty(x) Then
            x.Offset(0, -1) = x.Offset(-1, -1)
            x.Offset(0, -2) = x.Offset(-1, -2)
            End If
            Next
           
    End Sub
    Sub emptyrow()
      Dim ost As Long
      Dim i As Long
      Dim z As Integer
    
      ost = ActiveSheet.Cells.Find(what:="*", after:=Cells(1, 1), _
              searchdirection:=xlPrevious).Row
    
      For i = ost To 1 Step -1
    
        On Error Resume Next
    
        With ActiveSheet.Rows(i)
    
          z = .Find(what:="*", _
                     after:=.Parent.Cells(i, 1), _
                     searchdirection:=xlPrevious).Column
    
          If Err.Number <> 0 Then .Delete
    
        End With
        On Error GoTo 0
      Next i
    
    End Sub

  7. #7
    Registered User
    Join Date
    11-08-2011
    Location
    KSA
    MS-Off Ver
    Excel 2007
    Posts
    93

    Re: Help! combining 2 VBA codes

    Hi..the code was almost perfect but some issues

    1. If cell in column C is empty, corresponding columns A and B should also be deleted.
    2. empty cell in column A should copy the cell above it.
    Last edited by iamreese; 03-29-2012 at 06:32 AM.

  8. #8
    Forum Expert
    Join Date
    09-27-2011
    Location
    Poland
    MS-Off Ver
    Excel 2007
    Posts
    1,312

    Re: Help! combining 2 VBA codes

    please check attachment
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    11-08-2011
    Location
    KSA
    MS-Off Ver
    Excel 2007
    Posts
    93

    Re: Help! combining 2 VBA codes

    ^^ hi..see 2 issues on my prior post

    this conditions should be met:

    Columns("A:A").Select
        Selection.SpecialCells(xlCellTypeBlanks).Select
        Selection.FormulaR1C1 = "=R[-1]C"
        Columns("A:A").Select
        Selection.Copy
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Columns("B:B").Select
        Selection.SpecialCells(xlCellTypeBlanks).Select
        Application.CutCopyMode = False
        Selection.FormulaR1C1 = "=R[-1]C"
        Columns("B:B").Select
        Selection.Copy
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False

    Range("C2:BC" & Lastrow).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
    Last edited by iamreese; 03-29-2012 at 07:01 AM.

  10. #10
    Forum Expert
    Join Date
    09-27-2011
    Location
    Poland
    MS-Off Ver
    Excel 2007
    Posts
    1,312

    Re: Help! combining 2 VBA codes

    Hi
    I am uploading new file because in the previous one was mistake (the results are the same You wanted in your file call "results")

    p.s. I do not follow your last post....
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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