VBA delete old rows with certain value then replace with new rows

A

acerlaptop

New Member
Joined
Feb 17, 2020
Messages
44
Office Version
  1. 2013
Platform
  1. Windows
Hi All,

I have a file with macro in it. There are two different macros in it but I want to merge them using "Call". Both macros are working when run separately, but if I merge them, one of them stops with an error.

VBA Code: 
Dim r As Long
        For r = hlpR To 1 Step -1
            If Not Sheets("0872 Summary").Cells(r, lstCs + 1) = isBlank And _
            Not Sheets("0872 Summary").Cells(r, lstCs + 1) = WorksheetFunction.Text(Month(Sheets("GENERATOR").Range("D3")), "00") Then
                Rows(r).Delete 'with error when merged with the other Macro
            End If
        Next r

The firsts macro is named Sub RAW_DATA(). This is where I want to merge the second macro which is Sub LE0872_SUM_UPDATE(). The error comes from the second macro when I merge it with the first macro.

VBA Code: 
Sub RAW_DATA()
Application.ScreenUpdating = False

Call LE0872_SUM_UPDATE

Dim aCbs As Integer
Dim aRbs As Integer
Dim aCpl As Integer
Dim aRpl As Integer
Dim LRbooking As Integer
Dim LRraw As Integer
Dim lRbs As Integer
Dim lRpl As Integer

Sheets("Booking (RAW)").Activate
Range("A2:I2").Select
Selection.ClearContents
Rows(3 & ":" & Worksheets("Booking (RAW)").Rows.Count).Delete

'==========  BS [start]  ==========
Sheets("Booking").Activate
Range("E2").Select

LRbooking = Worksheets("Booking").Cells(Rows.Count, 1).End(xlUp).Row

Do While True
aRbs = ActiveCell.Row
aCbs = ActiveCell.Column
    If Cells(1, aCbs).Value = "Total" Then
        Cells(Rows.Count, aCbs).End(xlUp).Select
        lRbs = lRbs + 1
        Exit Do
    Else
        If aRbs > LRbooking Then
            Cells(2, aCbs + 1).Select
        Else
            If Selection.Value = 0 Or Selection.Value = "" Or Selection.Offset(0, -(aCbs - 2)).Value = "" Then
                Cells(aRbs + 1, aCbs).Select
            Else
                lRbs = Worksheets("Booking (RAW)").Cells(Rows.Count, 7).End(xlUp).Row
                Worksheets("Booking (RAW)").Range("A" & lRbs + 1) = Selection.Offset(0, -(aCbs - 1))
                Worksheets("Booking (RAW)").Range("B" & lRbs + 1) = Selection.Offset(0, -(aCbs - 2))
                Worksheets("Booking (RAW)").Range("C" & lRbs + 1) = Selection.Offset(0, -(aCbs - 3))
                Worksheets("Booking (RAW)").Range("D" & lRbs + 1) = Selection.Offset(0, -(aCbs - 4))
                Worksheets("Booking (RAW)").Range("E" & lRbs + 1) = Selection.Offset(-(aRbs - 1), 0)
                Worksheets("Booking (RAW)").Range("F" & lRbs + 1).FormulaR1C1 = "=LEFT(RC5,4)"
                Worksheets("Booking (RAW)").Range("F" & lRbs + 1).Value = Worksheets("Booking (RAW)").Range("F" & lRbs + 1).Value
                Worksheets("Booking (RAW)").Range("G" & lRbs + 1) = Selection
                Worksheets("Booking (RAW)").Range("H" & lRbs + 1).FormulaR1C1 = "=IF(RC7>0,""DR"",""CR"")"
                Worksheets("Booking (RAW)").Range("H" & lRbs + 1).Value = Worksheets("Booking (RAW)").Range("H" & lRbs + 1).Value
                Worksheets("Booking (RAW)").Range("I" & lRbs + 1).FormulaR1C1 = "=ROUND(ABS(RC7),2)"
                Worksheets("Booking (RAW)").Range("I" & lRbs + 1).Value = Worksheets("Booking (RAW)").Range("I" & lRbs + 1).Value
                Cells(aRbs + 1, aCbs).Select
            End If
        End If
    End If
Loop
'==========  BS [end]  ==========

'==========  PL [start]  ==========
Sheets("Booking").Activate
Range("E2").Select

Do While True
aRpl = ActiveCell.Row
aCpl = ActiveCell.Column
    If Cells(1, aCpl).Value = "Total" Then
        Cells(Rows.Count, aCpl).End(xlUp).Select
        lRpl = lRpl + 1
        Exit Do
    Else
        If aRpl > LRbooking Then
            Cells(2, aCpl + 1).Select
        Else
            If Selection.Value = 0 Or Selection.Value = "" Or Selection.Offset(0, -(aCpl - 2)).Value = "" Then
                Cells(aRpl + 1, aCpl).Select
            Else
                lRpl = Worksheets("Booking (RAW)").Cells(Rows.Count, 7).End(xlUp).Row
                Worksheets("Booking (RAW)").Range("A" & lRpl + 1) = Selection.Offset(0, -(aCpl - 3))
                Worksheets("Booking (RAW)").Range("B" & lRpl + 1) = Selection.Offset(0, -(aCpl - 4))
                Worksheets("Booking (RAW)").Range("C" & lRpl + 1) = Selection.Offset(0, -(aCpl - 1))
                Worksheets("Booking (RAW)").Range("D" & lRpl + 1) = Selection.Offset(0, -(aCpl - 2))
                Worksheets("Booking (RAW)").Range("E" & lRpl + 1) = Selection.Offset(-(aRpl - 1), 0)
                Worksheets("Booking (RAW)").Range("F" & lRpl + 1).FormulaR1C1 = "=LEFT(RC5,4)"
                Worksheets("Booking (RAW)").Range("F" & lRpl + 1).Value = Worksheets("Booking (RAW)").Range("F" & lRpl + 1).Value
                Worksheets("Booking (RAW)").Range("G" & lRpl + 1) = Selection * -1
                Worksheets("Booking (RAW)").Range("H" & lRpl + 1).FormulaR1C1 = "=IF(RC7>0,""DR"",""CR"")"
                Worksheets("Booking (RAW)").Range("H" & lRpl + 1).Value = Worksheets("Booking (RAW)").Range("H" & lRpl + 1).Value
                Worksheets("Booking (RAW)").Range("I" & lRpl + 1).FormulaR1C1 = "=ROUND(ABS(RC7),2)"
                Worksheets("Booking (RAW)").Range("I" & lRpl + 1).Value = Worksheets("Booking (RAW)").Range("I" & lRpl + 1).Value
                Cells(aRpl + 1, aCpl).Select
            End If
        End If
    End If
Loop
'==========  PL [end]  ==========

Sheets("Booking (RAW)").Activate
LRraw = Worksheets("Booking (RAW)").Cells(Rows.Count, 1).End(xlUp).Row
Range("A2:I2").Copy
Range("A2:I" & LRraw).PasteSpecial xlPasteFormats

Sheets("GENERATOR").Activate
MsgBox "Data conversion SUCCESSFUL!", vbOKOnly + vbInformation
Application.ScreenUpdating = True
ActiveWorkbook.Save

End Sub


VBA Code: 
Sub LE0872_SUM_UPDATE()
Application.ScreenUpdating = False

Dim actC As Integer
Dim actR As Integer
Dim hlpR As Integer
Dim lstCs As Integer
lstCs = Sheets("0872 Summary").Cells(1, Columns.Count).End(xlToLeft).Column
hlpR = Sheets("0872 Summary").Cells(Rows.Count, lstCs + 1).End(xlUp).Row

If hlpR = 1 Then
    Call MAIN_UPDATE
    Sheets("0872 Summary").Activate
    Range("A1").Select
    Sheets("GENERATOR").Activate
    Application.ScreenUpdating = True
    ActiveWorkbook.Save
Else
    If Not Sheets("0872 Summary").Cells(hlpR, lstCs + 1) = isBlank And _
        Sheets("0872 Summary").Cells(hlpR, lstCs + 1) = WorksheetFunction.Text(Month(Sheets("GENERATOR").Range("D3")), "00") Then
        Sheets("0872 Summary").Activate
        Range("A1").Select
        Sheets("GENERATOR").Activate
        Application.ScreenUpdating = True
        ActiveWorkbook.Save
    Else
        Dim r As Long
        For r = hlpR To 1 Step -1
            If Not Sheets("0872 Summary").Cells(r, lstCs + 1) = isBlank And _
            Not Sheets("0872 Summary").Cells(r, lstCs + 1) = WorksheetFunction.Text(Month(Sheets("GENERATOR").Range("D3")), "00") Then
                Rows(r).Delete
            End If
        Next r
        Call MAIN_UPDATE
        Sheets("0872 Summary").Activate
        Range("A1").Select
        Sheets("GENERATOR").Activate
        Application.ScreenUpdating = True
        ActiveWorkbook.Save
    End If
End If

End Sub

Sub MAIN_UPDATE()
Dim actC As Integer
Dim actR As Integer
Dim lstC As Integer
Dim lstR As Integer
Dim hlpC As Integer
lstC = Sheets("Booking").Cells(1, Columns.Count).End(xlToLeft).Column
lstR = 3
hlpC = Sheets("0872 Summary").Cells(1, Columns.Count).End(xlToLeft).Column

Sheets("Booking").Activate
Cells(1, lstC).Select
Do While True
actR = ActiveCell.Row
actC = ActiveCell.Column
If Cells(actR, actC).Value = "" Then
    Exit Do
Else
    If Selection.Value = 0 Then
        Cells(actR + 1, actC).Select
    Else
        If Selection.Offset(0, -(actC - 2)).Value = "0872" Or Selection.Offset(0, -(actC - 4)).Value = "0872" Then
            Rows(actR).Copy
            Sheets("0872 Summary").Activate
            Rows(lstR).Select
            Range("A" & lstR).Activate
            Selection.Insert Shift:=xlDown
            Cells(lstR, hlpC + 1).Value = WorksheetFunction.Text(Month(Sheets("GENERATOR").Range("D3")), "'00")
            Sheets("Booking").Activate
            Cells(actR + 1, actC).Select
        Else
            Cells(actR + 1, actC).Select
        End If
    End If
End If
Loop

End Sub
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
Sort by date Sort by votes
Hi, what error are you getting and on which line ?
 
Upvote 0
You must log in or register to reply here.

Similar threads

Z
  • Solved
VBA: Advanced Filter Limitations
Replies
3
Views
619
zero269
Z
S
  • Solved
VBA: reference a cell value in a different worksheet with alternative worksheet
Replies
7
Views
324
Peter_SSs
Peter_SSs
R
  • Question Question
Compose Email VBA Help
Replies
1
Views
52
Logit
Logit
M
  • Question Question
VBA - Find first empty cell in a row
Replies
2
Views
509
Mplz
M
O
  • Question Question
Creating workbooks for a team of staff
Replies
1
Views
200
HighAndWilder
H

Forum statistics

Threads
1,223,911
Messages
6,175,324
Members
452,635
Latest member
laura12345

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top