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.