Merging two Private Sub

DMO123

Board Regular
Joined
Aug 16, 2018
Messages
99
Hi All, i have 2 Private Sub Worksheet_change(ByVal Target As Range) codes that work on their own. i need them to work in the same sheet. whenever i do this the 2nd code does not run. how do i merge these please!!?


Code:
[Private Sub Worksheet_Change(ByVal Target As Range) Dim rng As Range, cel As Range
Set rng = Intersect(Target, Range([H2], Cells(Rows.Count, "H").End(xlUp)))


If rng Is Nothing Then Exit Sub
Application.EnableEvents = False
rng.Offset(, 1).FormulaR1C1 = "=IF(RC[-1]<>"""",R1C[6] & ""-"" &" & "TEXT(COUNTA(R2C[-1]:RC[-1]),""0000"") & ""-"" & R1C[7],"""")"
Application.EnableEvents = True
End Sub
Private Sub Movr_blanks_To_Bottom(ByVal Target As Range)
If Target.CountLarge > 1 Then Exit Sub
If Target.Column <> 9 Then Exit Sub
Range("A1", Range("A" & Rows.Count).End(xlUp)).Resize(, 11).Sort key1:=Range("I1"), order1:=xlAscending, Header:=xlYes
End Sub]

thanks!
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
.
I don't have the benefit of your workbook here to experiment with but this should work :

Code:
Private Sub Worksheet_Change(ByVal Target As Range) Dim rng As Range, cel As Range
Set rng = Intersect(Target, Range([H2], Cells(Rows.Count, "H").End(xlUp)))


If rng Is Nothing Then Exit Sub
Application.EnableEvents = False
rng.Offset(, 1).FormulaR1C1 = "=IF(RC[-1]<>"""",R1C[6] & ""-"" &" & "TEXT(COUNTA(R2C[-1]:RC[-1]),""0000"") & ""-"" & R1C[7],"""")"


If Target.CountLarge > 1 Then Exit Sub
If Target.Column <> 9 Then Exit Sub
Range("A1", Range("A" & Rows.Count).End(xlUp)).Resize(, 11).Sort key1:=Range("I1"), order1:=xlAscending, Header:=xlYes


Application.EnableEvents = True
End Sub
 
Upvote 0
Try:
Code:
Private Sub Worksheet_Change(ByVal Target As Range)    
    Dim x   As Long
    
    Application.EnableEvents = False
    
    x = Cells(Rows.Count, 8).End(xlUp).Row
    If Not Intersect(Target, Cells(2, 8).Resize(x - 1)) Is Nothing Then Target.Offset(, 1).FormulaR1C1 = "=IF(RC[-1]<>"""",R1C[6] &  ""-"" &" & "TEXT(COUNTA(R2C[-1]:RC[-1]),""0000"") & ""-"" & R1C[7],"""")"
    
    x = Cells(Rows.Count, 1).End(xlUp).Row
    If Target.CountLarge < 2 And Target.Column = 9 Then Cells(1, 1).Resize(x, 11).Sort key1:=Cells(1, 9), order1:=xlAscending, Header:=xlYes


    Application.EnableEvents = True
    
End Sub
 
Last edited:
Upvote 0
@Logit Thanks for posting. this code works for the auto population but stops after one is entered the sort does not work. no errors are showing it just stops working. Code 1 is auto populating column I when a value in H is present it populates the next number in the sequence for example Cell O has AAA and cell P has 2018 i enter a value in H2 and it populates AAA-0001-2018 and i do the same in H2 and in column I i get AAA-0002-2018. Code 2 is looking for blanks in column I if there is it moves the row to the bottom. so this would mean all blanks in column I is at the bottom.

thanks!
 
Last edited:
Upvote 0
Try:
Code:
Private Sub Worksheet_Change(ByVal Target As Range)    
    Dim x   As Long
    
    Application.EnableEvents = False
    
    x = Cells(Rows.Count, 8).End(xlUp).Row
    If Not Intersect(Target, Cells(2, 8).Resize(x - 1)) Is Nothing Then Target.Offset(, 1).FormulaR1C1 = "=IF(RC[-1]<>"""",R1C[6] &  ""-"" &" & "TEXT(COUNTA(R2C[-1]:RC[-1]),""0000"") & ""-"" & R1C[7],"""")"
    
    x = Cells(Rows.Count, 1).End(xlUp).Row
    If Target.CountLarge < 2 And Target.Column = 9 Then Cells(1, 1).Resize(x, 11).Sort key1:=Cells(1, 9), order1:=xlAscending, Header:=xlYes


    Application.EnableEvents = True
    
End Sub


thanks for posting! i tried this and it didn't work. no errors showed. i have explained above what both codes should do if this helps?
 
Upvote 0
It sounds like you may have inadvertantly disabled your events, and not re-enabled them, so your code is not firing.

Manually run this short code to turn them on again:
Code:
Sub ReEnable()
    Application.EnableEvents = True
End Sub
 
Upvote 0
It sounds like you may have inadvertantly disabled your events, and not re-enabled them, so your code is not firing.

Manually run this short code to turn them on again:
Code:
Sub ReEnable()
    Application.EnableEvents = True
End Sub

thanks for posting Joe4 i tried this and i still get the same results?
 
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,107
Members
452,302
Latest member
TaMere

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