Delete Contents of Cells
 

Hi,

Wonder if someone can help me please.

I have a spreadsheet with cells that look blank, they have do visible data
or formula in them however, when I run a macro to try and copy cells into
them, it fails.

I have therefore looked at just running a bit of code that within a given
range where it finds a blank cell it deletes the content.

Could someone have a look at the code below that I am using to see where i I
am going wrong because it doesn't seem to work.

Sub Delete_Cell_Contents()

Range("A2:I65536").Select
If Value = "" Then
Selection.ClearContents
End If

End Sub


Thanks very much

Chris

Delete Contents of Cells
 

Hi Chris,

Post the problematic code.


---
Regards,
Norman


"ir26121973" wrote in message
...
Hi,

Wonder if someone can help me please.

I have a spreadsheet with cells that look blank, they have do visible data
or formula in them however, when I run a macro to try and copy cells into
them, it fails.

I have therefore looked at just running a bit of code that within a given
range where it finds a blank cell it deletes the content.

Could someone have a look at the code below that I am using to see where i
I
am going wrong because it doesn't seem to work.

Sub Delete_Cell_Contents()

Range("A2:I65536").Select
If Value = "" Then
Selection.ClearContents
End If

End Sub


Thanks very much

Chris

Delete Contents of Cells
 

Hi Norman, thanks for getting back to me so quickly.

If you look at the end of my post the code is at the bottom.

Many thanks

Chris

"Norman Jones" wrote:

Hi Chris,

Post the problematic code.


---
Regards,
Norman


"ir26121973" wrote in message
...
Hi,

Wonder if someone can help me please.

I have a spreadsheet with cells that look blank, they have do visible data
or formula in them however, when I run a macro to try and copy cells into
them, it fails.

I have therefore looked at just running a bit of code that within a given
range where it finds a blank cell it deletes the content.

Could someone have a look at the code below that I am using to see where i
I
am going wrong because it doesn't seem to work.

Sub Delete_Cell_Contents()

Range("A2:I65536").Select
If Value = "" Then
Selection.ClearContents
End If

End Sub


Thanks very much

Chris

Delete Contents of Cells
 

Hi Chris,

I was referring to the the code mentioned he

however, when I run a macro to try and copy cells into
them, it fails.


---
Regards,
Norman



"ir26121973" wrote in message
...
Hi Norman, thanks for getting back to me so quickly.

If you look at the end of my post the code is at the bottom.

Many thanks

Chris

Delete Contents of Cells
 

Norman,

Apologies, this is the code:

Sub FindCopy()
Dim cl As Range
Dim myRange As Range
Set myRange = ActiveSheet.Range("$A2:A" & Range("$A65536").End(xlUp).Row)
For Each cl In myRange
If cl = "" Then
cl.Offset(-1, 0).EntireRow.Select
Selection.Copy
cl.Select
ActiveSheet.Paste
End If
Next cl
Range("$A$1").Select
End Sub

Norman, this code basically looks at a given range, if it comes across blank
cells in that range, it copies the row from above and pastes it in the blank
cells.

Regards

Chris

"Norman Jones" wrote:

Hi Chris,

I was referring to the the code mentioned he

however, when I run a macro to try and copy cells into
them, it fails.


---
Regards,
Norman



"ir26121973" wrote in message
...
Hi Norman, thanks for getting back to me so quickly.

If you look at the end of my post the code is at the bottom.

Many thanks

Chris

Delete Contents of Cells
 

Hi Chris,

Perhaps the 'blank' cells contain one or more spaces. Alternatively, perhaps
these cells contain a formula which returns an empty string.

Try:

'=============
Public Sub FindCopy()
Dim cl As Range
Dim myRange As Range

Set myRange = ActiveSheet.Range("A2:A" & _
Cells(Rows.Count, "A").End(xlUp).Row)
For Each cl In myRange
With cl
If Len(Trim(.Value)) = 0 Then
.Offset(-1, 0).EntireRow.Copy _
Destination:=.Item(1)
End If
End With
Next cl

End Sub
'<<=============


---
Regards,
Norman



"ir26121973" wrote in message
...
Norman,

Apologies, this is the code:

Sub FindCopy()
Dim cl As Range
Dim myRange As Range
Set myRange = ActiveSheet.Range("$A2:A" &
Range("$A65536").End(xlUp).Row)
For Each cl In myRange
If cl = "" Then
cl.Offset(-1, 0).EntireRow.Select
Selection.Copy
cl.Select
ActiveSheet.Paste
End If
Next cl
Range("$A$1").Select
End Sub

Norman, this code basically looks at a given range, if it comes across
blank
cells in that range, it copies the row from above and pastes it in the
blank
cells.

Regards

Chris

"Norman Jones" wrote:

Hi Chris,

I was referring to the the code mentioned he

however, when I run a macro to try and copy cells into
them, it fails.


---
Regards,
Norman



"ir26121973" wrote in message
...
Hi Norman, thanks for getting back to me so quickly.

If you look at the end of my post the code is at the bottom.

Many thanks

Chris

Delete Contents of Cells
 

Norman,

Thanks so very much much, this works a treat.

Kind regards, have a good day.

Chris

"Norman Jones" wrote:

Hi Chris,

Perhaps the 'blank' cells contain one or more spaces. Alternatively, perhaps
these cells contain a formula which returns an empty string.

Try:

'=============
Public Sub FindCopy()
Dim cl As Range
Dim myRange As Range

Set myRange = ActiveSheet.Range("A2:A" & _
Cells(Rows.Count, "A").End(xlUp).Row)
For Each cl In myRange
With cl
If Len(Trim(.Value)) = 0 Then
.Offset(-1, 0).EntireRow.Copy _
Destination:=.Item(1)
End If
End With
Next cl

End Sub
'<<=============


---
Regards,
Norman



"ir26121973" wrote in message
...
Norman,

Apologies, this is the code:

Sub FindCopy()
Dim cl As Range
Dim myRange As Range
Set myRange = ActiveSheet.Range("$A2:A" &
Range("$A65536").End(xlUp).Row)
For Each cl In myRange
If cl = "" Then
cl.Offset(-1, 0).EntireRow.Select
Selection.Copy
cl.Select
ActiveSheet.Paste
End If
Next cl
Range("$A$1").Select
End Sub

Norman, this code basically looks at a given range, if it comes across
blank
cells in that range, it copies the row from above and pastes it in the
blank
cells.

Regards

Chris

"Norman Jones" wrote:

Hi Chris,

I was referring to the the code mentioned he

however, when I run a macro to try and copy cells into
them, it fails.


---
Regards,
Norman



"ir26121973" wrote in message
...
Hi Norman, thanks for getting back to me so quickly.

If you look at the end of my post the code is at the bottom.

Many thanks

Chris

Delete Contents of Cells
 

Norman,

You will see from my later post that the macro works great.

Not knowing whether you are notified by email whether someone replies to you
I thought I would hang this off one of your earlier posts.

Like I said the code works great however it is copying some data that I
don't want to be copied because it is a unique Id.

Is there anyway please that I could tweek this code and actually use a
specific range. I'm new to VB so I'm picking up bits and bobs up where I can.

Many thanks

Chris

"Norman Jones" wrote:

Hi Chris,

Perhaps the 'blank' cells contain one or more spaces. Alternatively, perhaps
these cells contain a formula which returns an empty string.

Try:

'=============
Public Sub FindCopy()
Dim cl As Range
Dim myRange As Range

Set myRange = ActiveSheet.Range("A2:A" & _
Cells(Rows.Count, "A").End(xlUp).Row)
For Each cl In myRange
With cl
If Len(Trim(.Value)) = 0 Then
.Offset(-1, 0).EntireRow.Copy _
Destination:=.Item(1)
End If
End With
Next cl

End Sub
'<<=============


---
Regards,
Norman



"ir26121973" wrote in message
...
Norman,

Apologies, this is the code:

Sub FindCopy()
Dim cl As Range
Dim myRange As Range
Set myRange = ActiveSheet.Range("$A2:A" &
Range("$A65536").End(xlUp).Row)
For Each cl In myRange
If cl = "" Then
cl.Offset(-1, 0).EntireRow.Select
Selection.Copy
cl.Select
ActiveSheet.Paste
End If
Next cl
Range("$A$1").Select
End Sub

Norman, this code basically looks at a given range, if it comes across
blank
cells in that range, it copies the row from above and pastes it in the
blank
cells.

Regards

Chris

"Norman Jones" wrote:

Hi Chris,

I was referring to the the code mentioned he

however, when I run a macro to try and copy cells into
them, it fails.


---
Regards,
Norman



"ir26121973" wrote in message
...
Hi Norman, thanks for getting back to me so quickly.

If you look at the end of my post the code is at the bottom.

Many thanks

Chris

Delete Contents of Cells
 

Hi Chris,

Is there anyway please that I could tweek this code and actually use a
specific range. I'm new to VB so I'm picking up bits and bobs up where I
can.

Inedd you can. Simply replace:

Set myRange = ActiveSheet.Range("A2:A" & _
Cells(Rows.Count, "A").End(xlUp).Row)

For example, if the specific range of interest is A2:A10, then:

Set myRange = Activesheet.Range("A2:A10")

---
Regards,
Norman



"ir26121973" wrote in message
...
Norman,

You will see from my later post that the macro works great.

Not knowing whether you are notified by email whether someone replies to
you
I thought I would hang this off one of your earlier posts.

Like I said the code works great however it is copying some data that I
don't want to be copied because it is a unique Id.

Is there anyway please that I could tweek this code and actually use a
specific range. I'm new to VB so I'm picking up bits and bobs up where I
can.

Many thanks

Chris

"Norman Jones" wrote:

Hi Chris,

Perhaps the 'blank' cells contain one or more spaces. Alternatively,
perhaps
these cells contain a formula which returns an empty string.

Try:

'=============
Public Sub FindCopy()
Dim cl As Range
Dim myRange As Range

Set myRange = ActiveSheet.Range("A2:A" & _
Cells(Rows.Count, "A").End(xlUp).Row)
For Each cl In myRange
With cl
If Len(Trim(.Value)) = 0 Then
.Offset(-1, 0).EntireRow.Copy _
Destination:=.Item(1)
End If
End With
Next cl

End Sub
'<<=============


---
Regards,
Norman



"ir26121973" wrote in message
...
Norman,

Apologies, this is the code:

Sub FindCopy()
Dim cl As Range
Dim myRange As Range
Set myRange = ActiveSheet.Range("$A2:A" &
Range("$A65536").End(xlUp).Row)
For Each cl In myRange
If cl = "" Then
cl.Offset(-1, 0).EntireRow.Select
Selection.Copy
cl.Select
ActiveSheet.Paste
End If
Next cl
Range("$A$1").Select
End Sub

Norman, this code basically looks at a given range, if it comes across
blank
cells in that range, it copies the row from above and pastes it in the
blank
cells.

Regards

Chris

"Norman Jones" wrote:

Hi Chris,

I was referring to the the code mentioned he

however, when I run a macro to try and copy cells into
them, it fails.


---
Regards,
Norman



"ir26121973" wrote in message
...
Hi Norman, thanks for getting back to me so quickly.

If you look at the end of my post the code is at the bottom.

Many thanks

Chris

Delete Contents of Cells
 

Hi Norman,

This code works great. I'm looking towards restricting the columns. If
possible out of a range of columns A:I, I only want to copy columns A:G, but
for it still to go down the page as your original code did.

Hope this makes sense.

Regards

Chris

"Norman Jones" wrote:

Hi Chris,

Is there anyway please that I could tweek this code and actually use a
specific range. I'm new to VB so I'm picking up bits and bobs up where I
can.

Inedd you can. Simply replace:

Set myRange = ActiveSheet.Range("A2:A" & _
Cells(Rows.Count, "A").End(xlUp).Row)

For example, if the specific range of interest is A2:A10, then:

Set myRange = Activesheet.Range("A2:A10")

---
Regards,
Norman



"ir26121973" wrote in message
...
Norman,

You will see from my later post that the macro works great.

Not knowing whether you are notified by email whether someone replies to
you
I thought I would hang this off one of your earlier posts.

Like I said the code works great however it is copying some data that I
don't want to be copied because it is a unique Id.

Is there anyway please that I could tweek this code and actually use a
specific range. I'm new to VB so I'm picking up bits and bobs up where I
can.

Many thanks

Chris

"Norman Jones" wrote:

Hi Chris,

Perhaps the 'blank' cells contain one or more spaces. Alternatively,
perhaps
these cells contain a formula which returns an empty string.

Try:

'=============
Public Sub FindCopy()
Dim cl As Range
Dim myRange As Range

Set myRange = ActiveSheet.Range("A2:A" & _
Cells(Rows.Count, "A").End(xlUp).Row)
For Each cl In myRange
With cl
If Len(Trim(.Value)) = 0 Then
.Offset(-1, 0).EntireRow.Copy _
Destination:=.Item(1)
End If
End With
Next cl

End Sub
'<<=============


---
Regards,
Norman



"ir26121973" wrote in message
...
Norman,

Apologies, this is the code:

Sub FindCopy()
Dim cl As Range
Dim myRange As Range
Set myRange = ActiveSheet.Range("$A2:A" &
Range("$A65536").End(xlUp).Row)
For Each cl In myRange
If cl = "" Then
cl.Offset(-1, 0).EntireRow.Select
Selection.Copy
cl.Select
ActiveSheet.Paste
End If
Next cl
Range("$A$1").Select
End Sub

Norman, this code basically looks at a given range, if it comes across
blank
cells in that range, it copies the row from above and pastes it in the
blank
cells.

Regards

Chris

"Norman Jones" wrote:

Hi Chris,

I was referring to the the code mentioned he

however, when I run a macro to try and copy cells into
them, it fails.


---
Regards,
Norman



"ir26121973" wrote in message
...
Hi Norman, thanks for getting back to me so quickly.

If you look at the end of my post the code is at the bottom.

Many thanks

Chris