Completely OT - I ask in case there are any old or new VBA peeps about :)
Occasionally something comes across the desk that is out of the comfort zone,
I'm not a VBA guy, but with Googles help I'm almost there.
The issue is very specific, I want to select a rectangular range on a
worksheet; however I need to limit the rows to a number that will always change.
There is a neat way to do this using the following, which selects the "active"
stuff i.e. everything that is populated
ActiveSheet.Range("a1", _
ActiveSheet.Range("a1").End(xlDown).End(xlToRight)).Select?
...however, I want to limit the xlDown part to a cell I have in a variable (I'm
using a find and an offset to identify the last cell I want and creating a
variable from this); when I swap the variable inplace of xlDown it doesn't work
- I assume this is a limitation of that method.
My Macro code is here, no laughing.....
Sub Macro2()
Sheets("CSV").Select 'Select the sheet
Columns("A:A").Select 'Search column A
Cells.Find(What:="||||||||||||||||||||||||||||||||||||||||||||",
After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False _
, SearchFormat:=False).Activate ' Find the string and make cell active
Cells.FindNext(After:=ActiveCell).Activate 'Make cell active
strActive = ActiveCell.Address ' Set variable for Address for testing
MsgBox strActive ' output address for testing
ActiveCell.Offset(RowOffset:=-2).Activate 'go back up two rows
strActive2 = ActiveCell.Address 'set new variable for new cell address
MsgBox strActive2 ' output new cell address
'Now need to set the active area on the sheet using strActive2 as a row
limiter
'lastcol = ActiveSheet.Range("a1").End(xlToRight).Column
'lastrow = ActiveSheet.Cells("a18", lastcol).End(xlUp).Row
'ActiveSheet.Range("a1", ActiveSheet.Cells(lastrow, lastcol)).Select
ActiveSheet.Range("a1", _
ActiveSheet.Range("a1").End(xlDown).End(xlToRight)).Select
Selection.Copy
Workbooks.Add
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Application.CutCopyMode = False
ActiveWorkbook.SaveAs Filename:=FilePath & strFileName, _
FileFormat:=xlCSV, CreateBackup:=False
'Then need to saveas csv file type retaining the pipe separator
End Sub?
Stuart Watret
Offshore - IT Ltd
