vba：判斷行位置

code：

	Function ROWS_CHECK(SHEET_NAME, NUMBER_ADD) 'option sheet name、user rows number
	Y = Application.CountA(Sheets(SHEET_NAME).Range("1:1")) 'take a used rows.
	If Y = 0 Then 'option in empty
	TARGET_START = "A"
	TARGET_END = Split(Sheets(SHEET_NAME).Cells(1, Y + NUMBER_ADD).Address, "$")
	TARGET_END = TARGET_END(1)
	Else
	TARGET_START = TARGET_START(1) 'first rows
	TARGET_END = Split(Sheets(SHEET_NAME).Cells(1, Y + NUMBER_ADD).Address, "$") 'last rows
	TARGET_END = TARGET_END(1)
	End If
	ROWS_CHECK = TARGET_START & "@" & TARGET_END 'combine in function
	End Function

view raw option_header_alphabet hosted with ❤ by GitHub

使用範例：

'先設定需要用的表頭資訊

tag=Array("代號", "日期", "外資張數", "外資D3D", "外資D5D") 

'透過副程式取的需要使用的表頭英文字母代號

ROW_OUT = ROWS_CHECK("sheet1", 5) 

ROW_OUT = Split(ROW_OUT, "@")

SHEET_NAME = "sheet1"

'寫入表頭資料

Sheets(SHEET_NAME).Range(ROW_OUT(0) & 1 & ":" & ROW_OUT(1) & 1) = tag