PDA

View Full Version : Solved: Reduce size of current region



RECrerar
08-17-2009, 03:06 AM
Hi,

This is probaly really simple but I'm drawing a blank. I am using the following code to select the current region on a spreadsheet. However there is text there is a title offset by one cell from the region I am interested in and so currentRegion selects a row above the data and a column to the left of the data that I do not want. I am wondering how to change the size of the selected region to remove these unwanted cells.

For example, say the data I am interested in is in the range B4:G20, there is also text in cell A3 so current region selects A3:G20. I do not know where in the sheet the data I want is going to be, but I do know that it will always have this extra cell of text.

Hope that makes sense, also to let you k now I am writing the code in Word and using early binding to connect to Excel


Set Xl = GetObject(, "Excel.Application")
Xl.Workbooks(txFilename.Text).Activate
Set Wb = Xl.ActiveWorkbook

If obFullTable Then
With Wb.ActiveSheet
i = 1
Do Until .Range("C" & i).Text = "Description"
i = i + 1
Loop
.Range("C" & i).CurrentRegion.Select
End With
End If

Bob Phillips
08-17-2009, 03:28 AM
With wb.ActiveSheet
i = 1
Do Until .Range("C" & i).Text = "Description"
i = i + 1
Loop
With .Range("C" & i).CurrentRegion

.Cells(2, 2).Resize(.Rows.Count - 1, .Columns.Count - 1).Select
End With
End With

RECrerar
08-17-2009, 03:34 AM
Beautiful. Thanks xld