How to describe Active Cell in a " Do - Loop" macro

**Dorocica** · 06-24-2014, 10:32 AM

Hi,

I am trying to create a macro that runs on a report downloaded from our system and that needs to have 4 lines removed every 66 rows. What I have so far is:
Range("A2").Select
ActiveCell.Select
Range(Selection, Selection.End(xlDown)).Select
ActiveCell.Offset(66, 0).Rows("1:4").EntireRow.Select
Selection.Delete Shift:=xlUp

However, as this report is run by different people and on different accounts the length of the report and the place of the empty 4 lines varies in each report. How do I make sure all lines are removed and that macro runs on the whole report no matter what the length is?

Thanks!

**ImStevenB** · 06-24-2014, 01:18 PM

Hi,

If you have a column in the report that can be sorted, maybe create a macro that will sort out the blank lines.

Something like this:

Sub Sort_to_remove_blanks()

' Set this range for the top left and bottom most right corner of the report
' Use a row number that you know will be far enough down
Range("A2:J1000").Select
 ActiveWorkbook.Worksheets("Sheet2").Sort.SortFields.Clear
 ActiveWorkbook.Worksheets("Sheet2").Sort.SortFields.Add Key:=Range("A2:A1000"), _
    SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
 With ActiveWorkbook.Worksheets("Sheet2").Sort
    ' Set this range to the same as above so that the sort is done over all the rows
    .SetRange Range("A2:J1000")
    .Header = xlGuess
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
 End With

Range("A2").Select

End Sub

The thing in this is making sure you select far enough down on the bottom right range cell so that you are sure to cover whatever length the report may be.

I tried to do this dynamically with the xlDown to try and grab all the cells .. but any space or blank line is where it sets the range. So I opted for this as an example.

Hope this helps.

If anyone has another way to dynamically grab the range .. I would love to see it.

**JOHN H. DAVIS** · 06-24-2014, 03:28 PM

Maybe:

Sub Dorocica()
Dim i As Long
Range("A2").Select
Do Until ActiveCell.Row > Range("A" & Rows.count).End(3)(1).Row
    ActiveCell.offset(66).Resize(4).Interior.ColorIndex = 6
    ActiveCell.offset(66).Select
Loop
For i = Range("A" & Rows.count).End(3)(1).Row To 2 Step -1
    If Range("A" & i).Interior.ColorIndex = 6 Then
        Rows(i).Delete
    End If
Next i
End Sub

Test on a copy first to see if it does what you want.

How to describe Active Cell in a " Do - Loop" macro

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

How to describe Active Cell in a " Do - Loop" macro

Re: How to describe Active Cell in a " Do - Loop" macro

Re: How to describe Active Cell in a " Do - Loop" macro

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Excel 2010 -- "Visual Basic" "Macros" and "Record Macro" all disabled.

"Form and macro" to delete "Active" cell contents.

[SOLVED] Macro to look up and down from the active cell, copying the active "section" of the sheet

Macro "if" loop, change value cell if condition in other cell is met

The "Next Cell" command in a "For Each _ In _" loop

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions