Selecting a column in Excel 2

[coco86]

I'm using an Excel application object in Access to make modifications to a file prior to importing it. What I want to do is select the column after the final column (the number of columns if variable) but not the entire column. I don't want the first cell and I don't want any cells below the Current Region.

I'm new to Excel VBA but I've figured out one way of doing it:

    'find column after the last column
    myexcel.Selection.SpecialCells(xlCellTypeLastCell).Select
    myexcel.ActiveCell.Offset(-1, 1).Range("A1").Select
    'determine the range boundaries
    cell_1 = myexcel.ActiveCell.Address
    cell_2 = myexcel.ActiveCell.Offset(0 - myexcel.ActiveCell.Row + 2, _
             0).Range("A1").Address
    'select the range
    myexcel.Range(cell_1, cell_2).Select

I'd like something a little more "elegant" though, and more precise. If for some reason the current region contained blank columns or rows, I think I'd have a problem since I'm putting a formula into the range that references the cell to the left.

Any suggestions?
-Coco

 

[SkipVought]

Hi,

Try using CurrentRegion like this...

With Selection.Currentregion
   StartRow = .Row + 1
   EndRow = .Rows.Count + StartRow - 1
   TargetCol = .Columns.Count + .Column - 1
End With
Set rng = Range(Cells(StartRow, TargetCol), Cells(EndRow, TargetCol))

Hope this helps Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[Loomah]

Hi
Have a look at the FAQs on this forum, there are at least two describing ways to get the last/first row or column containing data.

Problems can arise using 'usedrange', 'currentregion' (as you seem to realise already) and 'lastcell'.

As an example the following will give the column after the last one containing any data

iCol = Cells.Find("*", [A1], , , xlByColumns, xlPrevious).Column + 1

I'm not 110% sure what you want your final result to be, however you already have a solution which works for you. I've edited your code a little to try and make it a little more 'elegant' just add back your refs to myexcel which i removed to work in excel.

'determine the range boundaries
cell_1 = Worksheets(1).Cells.SpecialCells(xlCellTypeLastCell).Offset(-1, 1).Address
cell_2 = Range(cell_1).Offset(0 - Range(cell_1).Row + 2, 0).Address
'select the range
Range(cell_1, cell_2).Select

Hope this helps a little

;-)
If a man says something and there are no women there to hear him, is he still wrong?

 

[coco86]

I like that, Thanks! -Coco

 

[coco86]

Just a follow-up, guys

I've gotten a little further into my book and came up with this:

Dim WorkingRange As Range
Dim OriginalRange As Range
Dim LastCell As String
Dim FirstCell As String
    
Set OriginalRange = Selection.CurrentRegion
    
LastCell = OriginalRange.Cells(OriginalRange.Cells.Count).Range("B1").Address
FirstCell = Range("A2").Address
    
Set MyRange = Range(FirstCell, LastCell)
    
MyRange.Columns(MyRange.Columns.Count).Select

What do you think?

-Coco

 

[SkipVought]

CoCo,

Try to stay away from Select and Activate -- they are not usually required and they slow your procedure down.

Here's a function that will reference the range you are looking for I believe, assuming that your table starts in A1 (Cells(1, 1))...

Function SetRangeObject() As Range
    Dim rng As Range
    With Cells(1, 1).CurrentRegion
        Set rng = Range(Cells(2, 1), Cells(.Rows.Count, .Columns.Count))
    End With
    Set SetRangeObject = rng
End Function
Sub TryItOut()
    SetRangeObject.Select
End Sub

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[coco86]

That function seems to work well, also.

As for using select, I think I have to, unless you can give me some guidance on how to accomplish the next steps without it. My goal is to strip the last character from each cell in the last column (I'm importing a text file that ends each row with "^".)

To do this, I'm putting a formula into the column to the right of the last column, then copying the cells and pasting them back on top of themselves as the value instead of the formula. Finally, I delete the original cells and shift the remaining cells to the left so the new value replaces the old.

Here's the code:
'paste into each cell the formula to strip the last
'character off the string to the left

Selection.FormulaR1C1 = "=LEFT(RC[-1],LEN(RC[-1])-1)"

'Copy the fields with the formula and then
'paste back on top of themselves the values
'of the formula.

Selection.Copy
Selection.PasteSpecial Paste:=xlValues
Application.CutCopyMode = False

'select the range

MyRange.Columns(MyRange.Columns.Count - 1).Select

'delete the original column

Selection.Delete Shift:=xlToLeft

-Coco

 

[SkipVought]

CoCo,

You don't need to do the formula stuff. If you want to replace the values in a column...

For Each r In YourRange
   With r
      .Value = Left(.Value, Len(.Value)-1)
   End With
Next

Hope this helps

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[coco86]

very, very cool!

Just one more thing, though. Can you tell me what I should dim r as?

-Coco

 

[SkipVought]

Sorry,

r is a Range (actually a single cell range in this case)

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com