Excel "A1" column reference question

Hi All,

Just a quick question for any Excel geeks out there: Is there a way to refer to a column using "A1" notation, but exclude a cell? For example, I want to select all of column G, except the FIRST row. The "G:G" range is ALMOST right, but I want something more like "G2:G" which isn't proper syntactically.

Thanks for any advices,

Milty

 

If you have entered column headings into the ?1 row you can use edit>goto (F5) to use special select.


This will give you a passel of options to only select those cells with numbers or formulae or whatever.

Post again with more specifics if you need more.

 

Sorry, should've mentioned this needs to be done programmatically, and I want to avoid interfacing with the shell at all costs. (ie, I could send keystrokes, but that'll reduce the reliability of the script I'm making)

 

by programming I assume you mean some processing to enter into a formula in another cell?
if you can distinguish by data type e.g text, number, currency etc youcan use the type function.

Thus
=type(a1) returns a number depending upon the data type in cell a1.

You can use this to select the entire range and then weed out those cells with inappropriate data cells.

Other useful functions are

istext.......returns true if text
isnumber...returns true if number
isnotext.....returns true of not text

You can combine these into your formula to exclude or eturn unity or zero values for the A1 etc cells.

I'm sorry I can't be more specific without more details of what you are actually trying to do.

 

I'm writing a small VBScript to format a .CSV file. Here's the code:

Code:

private sub date_format()
	dim o_excel
	set o_excel = createobject ("Excel.Application")
	'o_excel.visible = true
	o_excel.workbooks.open c_edidir & "cpo0002.csv"
	[COLOR="Red"]o_excel.range("G2:G10000").numberformat = "yyyymmddhhmmss"[/COLOR]	
	o_excel.activeworkbook.save
	o_excel.activeworkbook.saved = true
	o_excel.quit()
	set o_excel = nothing
end sub

The line highlighted in red is what I am trying to improve upon. Currently, it is hackish, and I would prefer something more explicit.

 

Although I'm not a VB expert i understand what you are saying.
I can't see you getting a more elegant (shorter) segment of script. If you must then you can use a for-next loop to examine the type function of the entire G column and only output to the CSV file if a particularly criterion is satisfied.

Incidentally Big Bazza was asking about Excel output to CSV

http://forums.majorgeeks.com/showthread.php?t=132877&highlight=excel

 

My issue isn't with datatypes at all. I'd just like to put everything in the G column (save the G1 cell) into the format specified here, regardless of content. (previous scripting will ensure the column's content is CLOSE to appropriate)

The cell reference is the issue.

 

since as far as I am aware there is no
'do not select' function or instruction
you have three options.

Firstly your current strategy - provide a comprhensive list of everything to select.

Secondly to select everything but exclude the data contained in certain selected cells by logically or numerically combining the data in a function with a selection criterion thus rendering it null.

Thirdly logical and or numerical manipulation can also be performed on stated ranges to create new values for the range. To do this you have to introduce some auxiliary variable, containing the output from for example an istext function.

You cant manipulate the ranges directly, only if you can determine something about the cell data that is unique for the ones you wish to eliminate. The simplest of these is datatype.

 

How about using the columns property.
http://msdn2.microsoft.com/en-us/library/aa214202(office.11).aspx
Then you can change whatever cells back to the formatting you want. You will have to add an extra line of code to change the formatting of G1 but this allows the whole column to be formatted.