Consulting

Results 1 to 6 of 6

Thread: Combine these codes to one

  1. #1
    VBAX Regular
    Joined
    Sep 2021
    Location
    INDIA
    Posts
    16
    Location

    Combine these codes to one

    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

  2. #2
    on Worksheet_Change sub, but it before the sub Ends:

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

  3. #3
    VBAX Regular
    Joined
    Sep 2021
    Location
    INDIA
    Posts
    16
    Location
    I have tried like this call serial

    After my excel workbook has crashed

  4. #4
    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

  5. #5
    VBAX Sage
    Joined
    Apr 2007
    Posts
    8,292
    Location
    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?
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  6. #6
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,417
    Don't mess with cells, use arrays.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •