Norman,

Thanks so much for helping me.

Unfortuantely, it's still not stopping at the end fo the second range - it
just keeps going.

I think this will work better as a navigation and selection exercise rather
than counts, ifs etc as the size of the first and second data sets plus the
number of columns between them is unpredictable, which is why I've used C5 as
a starting point and then xlDown etc.

I've put a fresh explanation as a reply to my original post, which should
help clarify.

Cheers,

Tom.

"Norman Jones" wrote:

Hi Mr. Tom,

Assuminhg that I have correctly understood your data structure, try:

'=============
Public Sub TestDelete()
Dim WB As Workbook
Dim SH As Worksheet
Dim Rng As Range
Dim delRng As Range
Dim rCell As Range
Dim CalcMode As Long

Set WB = ThisWorkbook
Set SH = WB.Sheets("Sheet2") '<<==== CHANGE
Set Rng = SH.Range("C5:C30") '<<==== CHANGE

On Error Resume Next
Set Rng = Rng.SpecialCells(xlBlanks)
On Error GoTo XIT

With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

If Not Rng Is Nothing Then
For Each rCell In Rng.Cells
If delRng Is Nothing Then
Set delRng = rCell.Resize(1, 11)
Else
Set delRng = Union(rCell.Resize(1, 11), delRng)
End If
Next rCell

If Not delRng Is Nothing Then
delRng.EntireRow.Delete
End If
End If
XIT:
With Application
.Calculation = CalcMode
.ScreenUpdating = True
End With
End Sub
'<<=============


---
Regards,
Norman


"mr tom" <mr-tom at mr-tom.co.uk.(donotspam) wrote in message
...
Plus, it does this for one column width.

I want to work with a range 11 cells wide. Only the leftmost cell will be
empty.

Length of first region of text is variable, so I used the xlDown twice,
once
to reach the end, Activecell.Offset (1,0) to move down to the first empty
cell and then xlDown to move to the end of the empty range (except it
includes the first populated cell of the next range...)

Not sure what the best approach is...

Tom.

"mr tom" wrote:

Thanks, Norman.

It's a little over-enthusiastic. I only wanted it to delete the empty
cells
between two populated areas, but it's done it for all populated areas
below
that.

Also - which bit do I amend to change how many cells per row to delete?

Cheers.

"Norman Jones" wrote:

Hi Mr Tom,

Try something like:

'=============
Public Sub TestDelete()
Dim WB As Workbook
Dim SH As Worksheet
Dim Rng As Range
Dim iRow As Long

Set WB = ThisWorkbook
Set SH = WB.Sheets("Sheet2") '<<==== CHANGE

With SH
iRow = .Range("C" & .Rows.Count).End(xlUp).Row
Set Rng = .Range("C5:C" & iRow)
End With
On Error Resume Next
Rng.SpecialCells(xlBlanks).Delete shift:=xlUp
On Error GoTo 0
End Sub
'<<=============


---
Regards,
Norman


"mr tom" <mr-tom at mr-tom.co.uk.(donotspam) wrote in message
...
Hi all,

I want a macro to start at a certain point, move down to the end of
the
text
and delete the empty space between it and the next populated cells.

Not delete rows, rahter delete cells, shifting up.

I've written:

Sub testdelete()

Range("C5").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Select
Range(Selection, Selection.End(xlDown)).Select
ActiveCell.Offset(-1, 0).Select
ActiveCell.Offset(0, 6).Select
Selection.Delete Shift:=xlUp
Range("C11").Select
End Sub

This doesn't work because the .select sets up a new selection rather
than
manipulating the old one.

I appreciate this is rookie stuff, but I don't write much VBA, so
I've
never
got very good at it.

Any ideas gratefully received.

Cheers.