0

Hello,
Today I put together some code for a VB macro, but, having tried different methods I can't get it to loop. The closest I could come was getting my 1st copy paste to do so infinitely. I'm trying to get it to copy the department name in front of every entry for that department, delete the subtotal Rows and the copy the next department name etc.

Range("A5").Select

If ActiveCell <> "" Then
ActiveCell.Select
Selection.Copy
        'If the cell has a Department, copy it'
ActiveCell.Offset(1, 0).Select
If ActiveCell = "" Then
         ActiveSheet.Paste
         'paste the department numbers on the rows without it.'

ElseIf ActiveCell Like "Totals*" Then
 Rows(ActiveCell.Row).delete
    'delete the Company Totals Rows'
    ActiveCell.Select
    
End If
 
End Sub

Thanks

2
Contributors
2
Replies
3
Views
9 Years
Discussion Span
Last Post by aktharshaik
0

The Range() uses a string for selection of a range of cells.

you can use like this for eg:

Dim i as integer
Dim n as integer

n = 100        'The last row

For i = 5 to n
    Range("A" & i).Select
    If ActiveCell <> "" Then
         ActiveCell.Select
         Selection.Copy
         'If the cell has a Department, copy it'
         ActiveCell.Offset(1, 0).Select
         If ActiveCell = "" Then
              ActiveSheet.Paste
              'paste the department numbers on the rows without it.'
         ElseIf ActiveCell Like "Totals*" Then

              'delete the Company Totals Rows'
              Rows(ActiveCell.Row).delete

             'Decrease the last row count
             'because a row is deleted
             n = n - 1

              ActiveCell.Select
          End If
    End If
Next i
This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.