Replies

Hans,
Thanks. That worked but I should have specified that I want that to happen for any value in A4 to A100
Can the code be modified for that range.
Thanks

Charlotte,
It was a typo. It is 3.6. I’ll try declaring them as you and Hans indicate.
Thanks

Thanks again, I’ll give it a try and see what it can do.

Thanks again, I’ll give it a try and see what it can do.

Francios,
Thanks.
SendKeys worked. If I have time I will try the api function.

Any ideas on removing orphaned code left behind after removing bcommand buttons.

Francios,
Thanks.
SendKeys worked. If I have time I will try the api function.

Any ideas on removing orphaned code left behind after removing bcommand buttons.

Hans,
Thanks. That did it.

Hans,
Thanks. That did it.

Hans,
I used the previous range you gave me and it works.
One more question and I’ll stop bugging you……today
How do I put all borders around that range. I tried creating a macro to see if that code would work but it didn’t like the .borders line.
.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic

Thanks,
Scott

Hans,
I used the previous range you gave me and it works.
One more question and I’ll stop bugging you……today
How do I put all borders around that range. I tried creating a macro to see if that code would work but it didn’t like the .borders line.
.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic

Thanks,
Scott

Huge screenshot reduced in size by HansV – please don’t exceed 640 x 480.

Hans,
I probably didn’t explain what I wanted to do correctly so I attached a picture of what I’m trying to do.
Your .Range suggestion just select a row or a column. I wanted to select the whole grid area from Cell E2 to the
last cell on the bottom right.
Thanks,
Scott

Huge screenshot reduced in size by HansV – please don’t exceed 640 x 480.

Hans,
I probably didn’t explain what I wanted to do correctly so I attached a picture of what I’m trying to do.
Your .Range suggestion just select a row or a column. I wanted to select the whole grid area from Cell E2 to the
last cell on the bottom right.
Thanks,
Scott

Hans,
Thanks that did it. It also fixed the problem of Excel releasing from memory.
One other thing
How would I find the last cell used in a range in this statement.
.Range(.Cells(2, 5), .Cells(last cell used)) like hitting Ctrl+End

Thanks as always,
Scott

Hans,
Thanks that did it. It also fixed the problem of Excel releasing from memory.
One other thing
How would I find the last cell used in a range in this statement.
.Range(.Cells(2, 5), .Cells(last cell used)) like hitting Ctrl+End

Thanks as always,
Scott

I have some code I use in Access after I export a query to an Excel file.
It does some formatting and only works once. If I try to do the process again it hangs
at the .Range statement.
I tried two different types of statements but the same thing happens.
Excel doesn’t seem to close out of memory after the code runs. Even stopping it from Ctl-Alt-Del and trying again.
The only time it seems to work again is the third time.
I’m not too good in Excel syntax so I’m sure I’m doing something wrong.

Scott

…Code that does the export happens here first……

    Dim xlApp As Excel.Application
    Dim xlWbk As Excel.Workbook
    Dim xlSht As Excel.Worksheet

        Set xlApp = CreateObject("excel.Application")
        Set xlWbk = xlApp.Workbooks.Open(strFILE)
        Set xlSht = xlWbk.Worksheets(1)
        
        xlApp.Visible = False
                
        With xlSht.Cells.Font
            .NAME = "Arial"
            .Size = 10
        End With
        With xlSht
            .Rows("1:1").RowHeight = 165
            '.Range("E1:AZ1").Select
            .Range(Cells(1, 5), Cells(1, intCount + 4)).Select
            'intCount is a count of the number of columns used.
            With Selection
                .HorizontalAlignment = xlGeneral
                .VerticalAlignment = xlBottom
                .WrapText = True
                .Orientation = 90
                .AddIndent = False
                .IndentLevel = 0
                .ShrinkToFit = False
                .ReadingOrder = xlContext
                .MergeCells = False
            End With
            Selection.Columns.AutoFit
            Columns("A:D").Select
            Selection.Columns.AutoFit

            Range("A1").Select
        End With
        
            xlWbk.Close SaveChanges:=True
            xlApp.Quit
            Set xlSht = Nothing
            Set xlWbk = Nothing
            Set xlApp = Nothing