Excel data selection

[Willie78]

Hi all i have recorded a macro see below

Sub newun()
ActiveCell.FormulaR1C1 = "=PROPER(RC[-2])&"" ""&PROPER(RC[-1])"
ActiveCell.Select
Selection.Copy
ActiveCell.Offset(0, -1).Range("A1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(0, 1).Range("A1").Select
Range(Selection, Selection.End(xlUp)).Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("A1").Select
End Sub

This works fine as long as the column to the left of the column i'm putting the formula into is always completed this may not always be the case. Is there a way of finding the bottom of the speadsheet then returning to the new column i have manually inserted then pasting in the formula. I'm guessing that ctrl and end finds me the bottom but i don't know how to record the number of columns i have moved so i can use this to get back to my original column..

I hope this makes sense

Cheers

Paul

 

[SkipVought]

Willie,

Please describe what you want to do. Where is your data?

I can only guess what you want from the code.

Skip,
[sub]
[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL![/sub]

 

[Willie78]

Sorry

On a regular basis I end up having to stick field together. Their postion changes from book to book hense why i created a releative macro rather than an abosolute one.

All it will do is add in the formula cell1&" "&cell2. this part works fine. I will then copy this down the length of my data (this also differs). To do so i have selected the column to left of the one with the formula in and hit end and down arrow. My problem is this only gives me the next available blank cell not the bottom of my data series which is where i need to be.

I hope this makes more sense

Cheers

Paul

 

[SkipVought]

Is this what you mean?

Sub newun()
    With ActiveCell
        With .CurrentRegion.CurrentRegion
            Set rng = Range(.Cells(1, 1), .Cells(1, 1).End(xlDown)).Offset(0, 2)
            With .Cells(1, 1)
                With .Offset(0, 2)
                    .FormulaR1C1 = "=PROPER(RC[-2])&"" ""&PROPER(RC[-1])"
                    .Copy
                End With
            End With
        End With
    End With
    rng.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
End Sub

Skip,
[sub]
[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL![/sub]

 

[Willie78]

Hi

That still isn't working quite right. Given that this is the data col c is

col a&" "&col b

col a col b col c
04/08/05 Speedy Express 34915 Speedy Express
10/01/05 Speedy Express 35074 Speedy Express
09/01/05
02/08/05 Speedy Express

When you copy col c down using fill row two copies ok when it hits row 3 it stops. I would like it to carry on to the end of the spreadsheet.

if you get my meaning

Cheers

Paul

 

[SkipVought]

It works for me
[tt]
4/8/2005 Speedy Express 4/8/2005 Speedy Express
10/1/2005 Speedy Express 10/1/2005 Speedy Express
9/1/2005 9/1/2005
2/8/2005 Speedy Express 2/8/2005 Speedy Express
[/tt]
with a change for the date...

Sub newun()
    With ActiveCell
        With .CurrentRegion.CurrentRegion
            Set rng = Range(.Cells(1, 1), .Cells(1, 1).End(xlDown)).Offset(0, 2)
            With .Cells(1, 1)
                With .Offset(0, 2)
                    .FormulaR1C1 = "=Text(PROPER(RC[-2]),""m/d/yyyy"")&"" ""&PROPER(RC[-1])"
                    .Copy
                End With
            End With
        End With
    End With
    rng.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
End Sub

Skip,
[sub]
[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL![/sub]

 

[Willie78]

Cheers for that

That seems to work fine now. I'm guessing that you are using the date column to calculate the end of the sheet and If this were to be empty then the formula wouldn't copy to the bottom?????

Cheers

Paul

 

[SkipVought]

You have to tell me what your data will look like!!!

1) you never told me that the first column was DATES

2) you never told me that any of the columns were not contiguous.

I cannot read your mind!

Please be SPECIFIC with your requirements!

Skip,
[sub]
[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL![/sub]

 

[Rofeu]

Hi,

instead of

 .Cells(1, 1).End(xlDown)

you might want to try

 .Cells(65536, 1).End(xlUp)

Cheers,

Roel

 

[barcon]

Is there a way of finding the bottom of the speadsheet

I like to use

ro = ActiveWorkbook.Worksheets(1).UsedRange.Rows.Count

to find the last used row on the worksheet. Then I can use the info to do other things. However, if you have used rows below the currently used area and later deleted the data, it will include that area as used too.

Barbara

 

[SkipVought]

Barbara,

UsedRange works fine if you are not doing certain delete methods. There are circumstances where UsedRange will give erroneous results.

Check thru the FAQ's

Skip,
[sub]
[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL![/sub]