UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Select A Number Of Columns Using Variables, Office 2013    
 
   
john_willmott
post Jun 25 2019, 11:15 AM
Post#1



Posts: 527
Joined: 12-July 03
From: South Wales, UK


I am writing some VBA to do a custom export from access 2013 into excel worksheet object.

Depending on the users choices when exporting, there is no set position for the data within a sheet. However, all the data is transferring, and I am trying to get format the sheet to make it look a bit more presentable.

I have two variables, lngFirstDataColumn, lngLastDataColumn.

I want to select columns lngFirstDataColumn to lngLastDataColumn so that I can set font size, centre the text

I have activated the sheet using wbExcel.Sheets(SheetRef).Activate

Is there a way to select a range of columns using variables - all the references seem to say columns("F:AD").select ????

I can loop through the columns and set properties one column at a time, but thought this would be quicker!

Any suggestions welcome!!
Go to the top of the page
 
ADezii
post Jun 25 2019, 12:00 PM
Post#2



Posts: 2,689
Joined: 4-February 07
From: USA, Florida, Delray Beach


QUOTE
Is there a way to select a range of columns using variables - all the references seem to say columns("F:AD").select ????

Yes there is. You need to convert the Numeric Values for the First & Last Columns to their Letter equivalents. Case in point (Code tested and operational):
CODE
Dim lngFirstDataColumn As Long
Dim lngLastDataColumn As Long
Dim strFirstDataColumn As String
Dim strLastDataColumn As String

lngFirstDataColumn = 40     'Column AN
lngLastDataColumn = 60      'Column BH

strFirstDataColumn = Split(Cells(1, lngFirstDataColumn).Address, "$")(1)
strLastDataColumn = Split(Cells(1, lngLastDataColumn).Address, "$")(1)

Worksheets("Sheet1").Columns(strFirstDataColumn & ":" & strLastDataColumn).Select

P.S. - The above Code is the same as
CODE
Columns("AN:BH").Select

This post has been edited by ADezii: Jun 25 2019, 12:23 PM
Go to the top of the page
 
john_willmott
post Jun 25 2019, 12:25 PM
Post#3



Posts: 527
Joined: 12-July 03
From: South Wales, UK


Thanks for the reply.

It seems such an obvious thing to be able to do. I thought I was going mad!!

I have done it by looping through the columns but it takes about 3 or 4 seconds per sheet and there are sometimes 20 sheets so there may well be a time saving if I do it once!

Will give it a try.

Cheers

john
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    15th November 2019 - 09:24 PM