Combine these codes to one

[prasadk]

Hi
Can anyone combine these two codes into one code in worksheet change event






Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Columns("B")) Is Nothing Then
Application.EnableEvents = False
Range("A2", Range("A" & Rows.Count).End(xlUp).Offset(2)).ClearContents
With Range("A2:A" & Range("B" & Rows.Count).End(xlUp).Row)
.Cells(1, 1).Value = 1
.DataSeries Rowcol:=xlColumns, Type:=xlLinear, Step:=1, Trend:=False
End With
Application.EnableEvents = True
End If
End Sub

Sub serial()
Set rRng = Range("A2:A" & Range("B" & Rows.Count).End(xlUp).Row) Dim cntr As Integer: cntr = 1 For Each cell In rRng If cell.EntireRow.Hidden = False Then Cells(cell.Row, 1).Value = cntr cntr = cntr + 1 End If Next
End sub

[arnelgp]

on Worksheet_Change sub, but it before the sub Ends:

...
...
End If
Call serial()
End Sub

[prasadk]

I have tried like this call serial

After my excel workbook has crashed

[arnelgp]

you separate each line to its own line:


Sub serial()
Dim rRng As Range, cell As Range
Set rRng = Range("A2:A" & Range("B" & Rows.Count).End(xlUp).Row)
Dim cntr As Long: cntr = 1
For Each cell In rRng
If cell.EntireRow.Hidden = False Then
Cells(cell.Row, 1).Value = cntr cntr = cntr + 1
End If
Next
End sub

[Paul_Hossler]

SOmethings are very confusing to me, like this line




Set rRng = Range("A2:A" & Range("B" & Rows.Count).End(xlUp).Row)


In words, what are you trying to do in the Change Event?

[snb]

Don't mess with cells, use arrays.