Member Avatar for phdbd

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
  • 290 Views
  • 5 Days Discussion Span
  • Latest Post Latest Post by aktharshaik

Recommended Answers

All 2 Replies

Member Avatar for aktharshaik

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
Member Avatar for aktharshaik

Check out the Example attached.


Regards
Shaik Akthar

LOOPING.zip (10.67 KB)
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.