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.

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.

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.

Hi Mr Tom,

To delete all empty cells in a stipulated range, try:

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

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

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
...
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.

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.

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.

Something like this ?

Private Sub CommandButton1_Click()
Dim StartRange As Range
Dim EndRange As Range

Const COLCOUNT As Long = 11

Set StartRange = Range("A1").End(xlDown).Offset(1, 0)
Set EndRange = StartRange.End(xlDown).Offset(-1, COLCOUNT)
Range(StartRange, EndRange).Delete shift:=xlUp

End Sub

NickHK

"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.

Thanks for all the replies.

All I need to do is make the following code work:

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, 11).Select
Selection.Delete Shift:=xlUp
End Sub

C5.Select is the start of the first range of data
The xldown takes me to the end of the range
The offset 1,0 takes me to the first blank cell at the bottom of the range

So far so good.

Range(Selection, Selection.End(xlDown)).Select takes me to the end of the
empty cells, selecting as it goes, but also selects the first cell of the
range of text below

So to try to counter that, I have used
ActiveCell.Offset(-1, 0).Select
But this doesn't work - it selects the top set of text, i.e.

a
b
c
d
e
f
g
_
_
_
_
_
h
i
j
k
l

I need to select all the _ (space) but the offset -1 selects 'g'

I then need to expand that selection to the right by 11 cells, to create a
range which is 11 cells wide and the number of empty cells deep.

I know how to subsequently delete them.

What am I doing wrong?

Cheers,

"mr tom" wrote:

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.

Hi Mr Tom

delRng.EntireRow.Delete

Should have read:

delRng.Delete shift:=xlUp


---
Regards,
Norman

Sorry - it gives a runtime error 1004 on this line:

Set StartRange = Range("A1").End(xlDown).Offset(1, 0)

I've added a post as a reply to my original one, which should explain what
I'm trying to do.

I think this should be simple navigation and selection within Excel, (which
is unfortunate that I'm struggling with).

Cheers,

Tom.

"NickHK" wrote:

Something like this ?

Private Sub CommandButton1_Click()
Dim StartRange As Range
Dim EndRange As Range

Const COLCOUNT As Long = 11

Set StartRange = Range("A1").End(xlDown).Offset(1, 0)
Set EndRange = StartRange.End(xlDown).Offset(-1, COLCOUNT)
Range(StartRange, EndRange).Delete shift:=xlUp

End Sub

NickHK

"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.

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.

Hi Mr Tom

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

You define the column C range of interest:

Set Rng = SH.Range("C5:C30") '<<==== CHANGE


---
Regards,
Norman



"mr tom" <mr-tom at mr-tom.co.uk.(donotspam) wrote in message
...
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.

Thanks so much, Norman.

I'm working on integrating it this pm (nothing is ever straightforward!)

I'll let you know how it goes.

Tom.

"Norman Jones" wrote:

Hi Mr Tom

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

You define the column C range of interest:

Set Rng = SH.Range("C5:C30") '<<==== CHANGE


---
Regards,
Norman



"mr tom" <mr-tom at mr-tom.co.uk.(donotspam) wrote in message
...
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.

It works! That's absolutely fantastic.

Thanks.

Tom.

"Norman Jones" wrote:

Hi Mr Tom

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

You define the column C range of interest:

Set Rng = SH.Range("C5:C30") '<<==== CHANGE


---
Regards,
Norman



"mr tom" <mr-tom at mr-tom.co.uk.(donotspam) wrote in message
...
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.