finding blank entries

[armagan]

Hi,

I'm trying to put together some VBA where it will search a specified column (column AE) for values. blank cells will be ignored, cells with a value will be cut and pasted to a new location (column AF).

can someone give me some suggestion with the code?

cheers,

[Norman Jones]

Hi Armagan,

Try something like:

'=============>>
Public Sub Tester()
Dim rng As Range

On Error Resume Next
Set rng = Range("AE:AE").SpecialCells(xlCellTypeConstants)
On Error Resume Next

If Not rng Is Nothing Then
rng.Copy Destination:=Range("AF1")
End If

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


---
Regards,
Norman



"armagan" <armagan.28d3pz_1148558402.6823@excelforum-nospam.com> wrote in
message news:armagan.28d3pz_1148558402.6823@excelforum-nospam.com...
>
> Hi,
>
> I'm trying to put together some VBA where it will search a specified
> column (column AE) for values. blank cells will be ignored, cells with
> a value will be cut and pasted to a new location (column AF).
>
> can someone give me some suggestion with the code?
>
> cheers,
>
>
> --
> armagan
> ------------------------------------------------------------------------
> armagan's Profile:
> http://www.excelforum.com/member.php...o&userid=34237
> View this thread: http://www.excelforum.com/showthread...hreadid=545448
>

[Alf Bryn]

Or somthing like this:

Sub Macro1()
'
Dim cell As Range

For Each cell In Range("AE1:AE20")
If cell.Value <> "" Then
cell.Cut cell.Offset(0, 1)
End If
Next cell
'
End Sub


"Norman Jones" <normanjones@whereforartthou.com> wrote in message
news:eo9aVc$fGHA.2456@TK2MSFTNGP04.phx.gbl...
> Hi Armagan,
>
> Try something like:
>
> '=============>>
> Public Sub Tester()
> Dim rng As Range
>
> On Error Resume Next
> Set rng = Range("AE:AE").SpecialCells(xlCellTypeConstants)
> On Error Resume Next
>
> If Not rng Is Nothing Then
> rng.Copy Destination:=Range("AF1")
> End If
>
> End Sub
> '<<=============
>
>
> ---
> Regards,
> Norman
>
>
>
> "armagan" <armagan.28d3pz_1148558402.6823@excelforum-nospam.com> wrote in
> message news:armagan.28d3pz_1148558402.6823@excelforum-nospam.com...
>>
>> Hi,
>>
>> I'm trying to put together some VBA where it will search a specified
>> column (column AE) for values. blank cells will be ignored, cells with
>> a value will be cut and pasted to a new location (column AF).
>>
>> can someone give me some suggestion with the code?
>>
>> cheers,
>>
>>
>> --
>> armagan
>> ------------------------------------------------------------------------
>> armagan's Profile:
>> http://www.excelforum.com/member.php...o&userid=34237
>> View this thread:
>> http://www.excelforum.com/showthread...hreadid=545448
>>
>
>

[Norman Jones]

Hi Armagan,

The suggested code copies rather than cuts. Therefore, instead try :

'=============>>
Public Sub Tester001A()
Dim rng As Range

On Error Resume Next
Set rng = Range("AE:AE").SpecialCells(xlCellTypeConstants)
On Error Resume Next

If Not rng Is Nothing Then
With rng
.Copy Destination:=Range("AF1")
.ClearContents
End With
End If

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


---
Regards,
Norman


"Norman Jones" <normanjones@whereforartthou.com> wrote in message
news:eo9aVc$fGHA.2456@TK2MSFTNGP04.phx.gbl...
> Hi Armagan,
>
> Try something like:
>
> '=============>>
> Public Sub Tester()
> Dim rng As Range
>
> On Error Resume Next
> Set rng = Range("AE:AE").SpecialCells(xlCellTypeConstants)
> On Error Resume Next
>
> If Not rng Is Nothing Then
> rng.Copy Destination:=Range("AF1")
> End If
>
> End Sub
> '<<=============
>
>
> ---
> Regards,
> Norman

[armagan]

both ideas are good,

but I've just realised I want to insert a row above when I find a value in the column (and copy to the row above). Although generally the same formulas will work, it throws off the formula, as once the row is inserted it changes the selected cell.

how would you modify the existing formula to account for this?

Thanks for your help!

Armagan

[Alf Bryn]

Not sure I understand what you want to do but if you chage the line

cell.Cut cell.Offset(0, 1) to cell.Cut cell.Offset(-1, 1) a value found
in AE4 will be placed in AF3.

Be aware that that if you have data in cell AE1 this will not work.



"armagan" <armagan.28d91p_1148565304.6689@excelforum-nospam.com> wrote in
message news:armagan.28d91p_1148565304.6689@excelforum-nospam.com...
>
> both ideas are good,
>
> but I've just realised I want to insert a row above when I find a value
> in the column (and copy to the row above). Although generally the same
> formulas will work, it throws off the formula, as once the row is
> inserted it changes the selected cell.
>
> how would you modify the existing formula to account for this?
>
> Thanks for your help!
>
> Armagan
>
>
> --
> armagan
> ------------------------------------------------------------------------
> armagan's Profile:
> http://www.excelforum.com/member.php...o&userid=34237
> View this thread: http://www.excelforum.com/showthread...hreadid=545448
>

[armagan]

Alf,
I want to modify your formula so that when it searches this column for values, when it finds a value. I want it to insert a new row just above the value and cut and paste the 'found' value to a cell in that new row (in column AD).

hope that makes sense.

Armagan

[Alf Bryn]

Hi armagan!

The line cell.Cut cell.Offset(0, 1) "cuts" the value and place it according
to "offset" parameter.

The offset parameter is a coordinate relative to the cell you cut. The first
number is the row and the second is the column. Numbers can range from
negative to positive.

The cell you "cut" has always the offset 0, 0 and if you would like to put
the "cut" value 3 rows below and 5 columns to the right the "offset" command
would be Offset(3, 5).

To put it 1 row up and 3 columns to the left you do this by Offset(-1, -3)

Offset will not work outside the rows and columns of the spreadsheet. If you
"cut" a value from say A1 you can not use a negative row "offset" since
there is no row above row 1. So always check using "offset" that you don't
try to place values outside the rows and columns in a spreadsheet.

Have modified the macro so it will insert a line and and place the "cut"
value on the row above and in column AD as you wrote. As fore the range it
will expand since you are putting a new line in it every time there is a
value in column AE so I think you better use Norman Jones range definition.
That is substitue in my example Range("AE2:AE15") with Range("AE:AE").

Don't forget that if you have a value in cell AE1 a row "offset" of -1 will
not work.



Sub Macro1()
'
Dim cell As Range

For Each cell In Range("AE2:AE15")
If cell.Value <> "" Then
cell.EntireRow.Insert
cell.Cut cell.Offset(-1, -1)
End If
Next cell
'
End Sub





"armagan" <armagan.28f162_1148648419.2457@excelforum-nospam.com> wrote in
message news:armagan.28f162_1148648419.2457@excelforum-nospam.com...
>
> Alf,
> I want to modify your formula so that when it searches this column for
> values, when it finds a value. I want it to insert a new row just
> above the value and cut and paste the 'found' value to a cell in that
> new row (in column AD).
>
> hope that makes sense.
>
> Armagan
>
>
> --
> armagan
> ------------------------------------------------------------------------
> armagan's Profile:
> http://www.excelforum.com/member.php...o&userid=34237
> View this thread: http://www.excelforum.com/showthread...hreadid=545448
>