Worksheet Rows
 

Not really, I just don't want any rows after my data finishes, so that when I
do a pivot table bassed on column A:D it doesn't bringing in the very
annoying (Blank) column. Doing this through VB with a changing amount of data
so have to select the columns instead of range of data.

Thanks........ Alastair.

"Zack Barresse" wrote:

Hi Alastair79,

If you use AutoFilter and filter for blank rows, you can then select your
data, press F5 | special | blank cells | Ctrl + - (minus) | Entire row | Ok.

Is this what you're looking for?

--
Regards,
Zack Barresse, aka firefytr (MVP: Excel)


"Alastair79" wrote in message
...
Is it possiable to shorten a worksheet to that it doesn't empty rows after
your Data has finished. I don't want to hide them but remove them
altogether!!

Thanks.......... Alastair.

Worksheet Rows
 

Alistair,

To answer your original question, No, you can't change the size of a
worksheet. However, there is a solution to your problem.

Create a dynamic name for your pivot table Data Source using the
'Offset' formula. The formula in your 'Refers To:' field should look
something like this...

=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),4)

where...
Sheet1 is the name of the sheet containing the data
$A$1 is the top left of your data
$A:$A contains no blank cells within the data list and no populated
cells outside of the data list

Refer to online Help for a full explanation of the OFFSET Worksheet
function

HTH,
Nick.

Worksheet Rows
 

This function will give you the last populated cell in a sheet.

Public Function LastCell(Optional ByVal wks As Worksheet) As Range
Dim lngLastRow As Long
Dim intLastColumn As Integer

If wks Is Nothing Then Set wks = ActiveSheet
On Error Resume Next
lngLastRow = wks.Cells.Find(What:="*", _
After:=wks.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
intLastColumn = wks.Cells.Find(What:="*", _
After:=wks.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Column
On Error GoTo 0
If lngLastRow = 0 Then
lngLastRow = 1
intLastColumn = 1
End If
Set LastCell = wks.Cells(lngLastRow, intLastColumn)
End Function

With this you can write a simple function to delete (not clear contents) all
of the rows beneath and to the right of this cell.

Public Sub CompactSheet(Optional ByVal wks As Worksheet)
Dim rng As Range

If wks Is Nothing Then Set wks = ActiveSheet
Set rng = LastCell(wks)
wks.Range(rng.Offset(0, 1), wks.Cells(1,
Columns.Count)).EntireColumn.Delete
wks.Range(rng.Offset(1, 0), wks.Cells(Rows.Count, 1)).EntireRow.Delete
End Sub


--
HTH...

Jim Thomlinson


"Alastair79" wrote:

Not really, I just don't want any rows after my data finishes, so that when I
do a pivot table bassed on column A:D it doesn't bringing in the very
annoying (Blank) column. Doing this through VB with a changing amount of data
so have to select the columns instead of range of data.

Thanks........ Alastair.

"Zack Barresse" wrote:

Hi Alastair79,

If you use AutoFilter and filter for blank rows, you can then select your
data, press F5 | special | blank cells | Ctrl + - (minus) | Entire row | Ok.

Is this what you're looking for?

--
Regards,
Zack Barresse, aka firefytr (MVP: Excel)


"Alastair79" wrote in message
...
Is it possiable to shorten a worksheet to that it doesn't empty rows after
your Data has finished. I don't want to hide them but remove them
altogether!!

Thanks.......... Alastair.