Selecting Rows in Excel Using Relative References

nmilmine,

This may help -- I would submit an answer but I don't think I know enough about the record and data fields -- also do not have any error checking --


This may help you though---

  Range("A1").Select
  Do
   Do Until (ActiveCell = "")
       ActiveCell.Offset(1, 0).Select
   Loop
   If Not (ActiveCell.Offset(1, 0) = "") Then
    ActiveCell.Offset(0, 0).Range("A1:A3").Select
    Selection.EntireRow.Delete
   Else
     Exit Do
   End If
  Loop

Well, here's another way to do it...

Sub DeleteRowsRoutine()
Dim c As Range, R As Range
Dim LastRow As Integer
For Each R In ActiveSheet.UsedRange.rows
For Each c In R.rows("1:4").Cells
If c <> "" Then GoTo NextRow
Next c
LastRow = R.Row - 1
GoTo DeletionLoop
NextRow:
Next R
DeletionLoop:
If LastRow = 0 Then LastRow = ActiveSheet.UsedRange.rows.Count
For i = LastRow To 1 Step -1
For Each c In rows(i).Cells
If c <> "" Then GoTo GoOn
Next c
rows(i & ":" & i + 2).Delete
GoOn:
Next i
End Sub

It checks complete rows for emptyness, but first determines where the last row of the information block is, assigns the last row number before 4 empty rows to the variable LastRow andd then performs the deletion upwards.

Deleting upwards is better than deleting downwards, because when going down, the first row after three deleted ones would not be treated anymore.

Anyway, try it and report back

Have Fun
Calacuccia

Hi Calacuccia

I copied your code to a module but, while it runs, nothing actually happens to the data.

The screen flashes as though something is happening but no changes occur

Regards
Neil

Hi Neil

Does the data contain complete empty rows of data or do you have empty cells with filled cels on same row but further to the right ?

In that case the macro won't do anything, it only checks complete empty rows.

Second possibility, do you have 4 empty rows at the top of your sheet. In that case the macro will do nothing neither, as it considers the 4 rows without data as the end of the to be processed block.

I retested the macro and it works perfect for me....

Waiting for more feedback

Calacuccia

Hi Calacuccia

This is a typical block copied from column A of the spreadsheet.  There is also information in columns B-I.  There is no information in the blank rows.  There are no blank rows at the start of the spreadsheet.

HPC8187
HPC9788
HPC9793

HEWLETT-PACKARD - HP Vectra Series - HP Vectra VLI8 Series

HPC8697
HPC8696
HPC9456

I am happy to email you the spreadsheet if you wish.

The running of the macro does not move the position of the starting cell, no matter which cell I start it from in column A.

Thanks for your help so far
Neil

Copy of all cells for same block

HPC8187      D8187N      HP Vectra VEi8 PIII 550 64Mb 8.4Gb HDD Soundcard  10/100 NIC Desktop NT4      2726.12      3587      Computer Systems      HEWLETT-PACKARD            10 days
HPC9788      D9788A      HP Vectra VEi8 PIII 600E 64Mb 13.5Gb HDD 48X CD  Soundcard Desktop Win95      2599.2      3420      Computer Systems      HEWLETT-PACKARD            10 days
HPC9793      D9793A      HP Vectra VEi8 PIII 650 64Mb 13.5Gb HDD 48X CD  Soundcard Desktop Win95      3644.2      4795      Computer Systems      HEWLETT-PACKARD            10 days

HEWLETT-PACKARD - HP Vectra Series - HP Vectra VLI8 Series                                                

HPC8697      D8697A      HP Vectra VLi8 Celeron 466a 64Mb 8.4Gb HDD 24X CD  Soundcard 10/100 NIC Small Form Factor Win95      1875.68      2468      Computer Systems      HEWLETT-PACKARD            10 days
HPC8696      D8696A      HP Vectra VLi8 Celeron 466a 32Mb 4.3Gb HDD  Soundcard 10/100 NIC Small Form Factor Win95      1824      2400      Computer Systems      HEWLETT-PACKARD            10 days
HPC9456      D9456A      HP Vectra VLi8 Celeron 500a 64Mb 8.4Gb HDD 24X CD  Soundcard 10/100 NIC Small Form Factor Win95      1928.12      2537      Computer Systems      HEWLETT-PACKARD            10 days

Hi Neill,

You can send me a copy to geert.dumortier7@yucom.be

I've tried an example similar to yours and just found an error. As I'm going up in he spreadsheet instead of going down, with your example, the three lines to be deleted are misinterpreted as first the bottom empty line and following two are deleted, then the top empty line and three following. But that does not match your observations neither, as you tell me nothing happened.

Before sending me the file, try this code:

Sub DeleteRowsRoutine()
Dim c As Range, R As Range
Dim LastRow As Integer
Dim i As Integer
For Each R In ActiveSheet.UsedRange.Rows
For Each c In R.Rows("1:4").Cells
If c <> "" Then GoTo NextRow
Next c
LastRow = R.Row - 1
GoTo DeletionLoop
NextRow:
Next R
DeletionLoop:
If LastRow = 0 Then LastRow = ActiveSheet.UsedRange.Rows.Count
While i < LastRow
i = i + 1
For Each c In Rows(i).Cells
If c <> "" Then GoTo GoOn
Next c
Rows(i & ":" & i + 2).Delete
i = i - 1
LastRow = LastRow - 3
GoOn:
Wend

The macro is not written to move to the cells, it just deletes rows without moving the active cell.

If you like to see the progress, change the macro to:

Sub DeleteRowsRoutine()
Dim c As Range, R As Range
Dim LastRow As Integer
Dim i As Integer
For Each R In ActiveSheet.UsedRange.Rows
For Each c In R.Rows("1:4").Cells
If c <> "" Then GoTo NextRow
Next c
LastRow = R.Row - 1
GoTo DeletionLoop
NextRow:
Next R
DeletionLoop:
If LastRow = 0 Then LastRow = ActiveSheet.UsedRange.Rows.Count
While i < LastRow
i = i + 1
Rows(i).Select
For Each c In Rows(i).Cells
If c <> "" Then GoTo GoOn
Next c
Rows(i & ":" & i + 2).Delete
i = i - 1
LastRow = LastRow - 3
GoOn:
Wend

Cheers & Good Luck
Calacuccia

Adjusted points from 50 to 100

Hi Calacuccia

The last sets of code made more sense.  I think that something was happening but I just couldn't see it so didn't believe it.

I have increased the points because I have just discovered that sometimes there are 5 lines that need removing.(don't you hate this kind of customer)

The only way to determine if a line should be removed is if there is nothing in the B column.

It would search down the B column and if there is nothing in the cell, make a selection down as many rows as there are cells in the B column that have nothing in them, delete these rows and then carry on ........
stopping when there are say more than 10 cells in the range it finds (meaning that you are at the end of the data

Thanks for your help to date
Neil

Thanks Calacuccia, its works great

You're Wellcome, Neil.

Calacuccia