Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy rows from one worksheet automatically, ignore rows that are b | Excel Worksheet Functions | |||
instering rows in one worksheet that reflect another worksheet | Excel Worksheet Functions | |||
insert rows in a worksheet that do not change adjoining rows | Excel Discussion (Misc queries) | |||
How do I insert blank rows between rows in completed worksheet? | Excel Discussion (Misc queries) | |||
Why does rngDataSource.Rows.Count = 65536 when worksheet Rows=95? | Excel Discussion (Misc queries) |