0

I need help with the VBA code. I need to copy rows(A2: A4) and paste all the three rows from A5 and so on using the input box(max 200 times). I need to hide all the 600 rows and need to unhide only when selecting the input box. ( for ex:- if I enter 5 times in the input box. VBA to paste the A2: A4 five times and hide the rest of the rows).

This is the example code I'm working on. I know it's all wrong.

enter image description here

    '>>>>>>>>>>IF CHANGES HIDE UNUSED ROWS
    If Not Intersect(Target, Range("A70:A89")) Is Nothing Then
        'Select First Row
            Range("N70").Select
        'Set Variables
            y = 1
7
            y = y + 1
        'If Correct Number of Rows have been counted goto end of sub
            If y = 22 Then
                GoTo 8
            End If
        'Is Active Data Row REQUIRED
            X = ActiveCell.Value
        'If Not then Hide Row
            If X = "1" Then
                Selection.EntireRow.Hidden = True
            End If
        'If yes then unhide
            If X = "" Then
                Selection.EntireRow.Hidden = False
            End If
        'Go to Next Row & Repeat Above
            ActiveCell.Offset(1, 0).Select
            GoTo 7

8
    End If


'Go Back To Original Active Cell
    Range(cell).Select
   Application.ScreenUpdating = True
PeterT
  • 8,232
  • 1
  • 17
  • 38
Srinivas k
  • 13
  • 5
  • 3
    Start with [avoiding Select/Activate](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba), then carefully de-tangle the execution paths and replace the `GoTo`-jumping with an actual loop. – Mathieu Guindon Sep 19 '19 at 17:36
  • You can also use the advanced filter method to apply/remove filters based on the input. https://learn.microsoft.com/en-us/office/vba/api/excel.range.advancedfilter – Jenn Sep 19 '19 at 18:20
  • I'm looking for a code to copy the entire rows A4:A6. the rows need to be copied n times. Say 50 times in the active sheet. The paste should start from A7 and so on... Appreciate your help in advance. – Srinivas k Sep 21 '19 at 12:55

0 Answers0