Clear contents of last two rows of data on sheet

Hi. 

I'm using v 2003. 

I need a couple of lines of code that will delete or clear the contents of 
the last two rows on a sheet that have data in them. In other words, if there 
are 800 lines of data and I want rows 799 and 800 to be deleted or contents 
cleared. The number of the last row varies everytime I run the report.

Can anyone help? Thanks!

Dani
0
Utf
3/11/2010 7:06:01 PM
excel.programming 6508 articles. 2 followers. Follow

6 Replies
859 Views

Similar Articles

[PageSpeed] 1

Hi Dani,

Here are 2 options. See the comments for when to use the option.

'Option 1
Sub DeleteRows1()
'When a specific column will always have data in last row.

Dim lastRow As Long

With Sheets("Sheet1")
  'Can replace "A" with any column that
  'will always have data in the last row.
  lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
  .Rows(lastRow - 1 & ":" & lastRow).Delete

End With

End Sub


'Option 2
Sub DeleteRows2()

'If it is not know which column
'will always have data in the last row.

Dim lastRow As Long

With Sheets("Sheet1")
  
  lastRow = .Cells _
    .Find(What:="*", _
    After:=.Cells(1, 1), _
    LookIn:=xlFormulas, _
    LookAt:=xlPart, _
    SearchOrder:=xlByRows, _
    SearchDirection:=xlPrevious, _
    MatchCase:=False, _
    SearchFormat:=False).Row
    
  .Rows(lastRow & ":" & lastRow - 1).Delete
  
End With

End Sub

-- 
Regards,

OssieMac


0
Utf
3/11/2010 7:36:02 PM
The first option works great! Thanks! I should've specified that there is 
always data in column A. 

Appreciate your help!

Dani

"OssieMac" wrote:

> Hi Dani,
> 
> Here are 2 options. See the comments for when to use the option.
> 
> 'Option 1
> Sub DeleteRows1()
> 'When a specific column will always have data in last row.
> 
> Dim lastRow As Long
> 
> With Sheets("Sheet1")
>   'Can replace "A" with any column that
>   'will always have data in the last row.
>   lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
>   .Rows(lastRow - 1 & ":" & lastRow).Delete
> 
> End With
> 
> End Sub
> 
> 
> 'Option 2
> Sub DeleteRows2()
> 
> 'If it is not know which column
> 'will always have data in the last row.
> 
> Dim lastRow As Long
> 
> With Sheets("Sheet1")
>   
>   lastRow = .Cells _
>     .Find(What:="*", _
>     After:=.Cells(1, 1), _
>     LookIn:=xlFormulas, _
>     LookAt:=xlPart, _
>     SearchOrder:=xlByRows, _
>     SearchDirection:=xlPrevious, _
>     MatchCase:=False, _
>     SearchFormat:=False).Row
>     
>   .Rows(lastRow & ":" & lastRow - 1).Delete
>   
> End With
> 
> End Sub
> 
> -- 
> Regards,
> 
> OssieMac
> 
> 
0
Utf
3/11/2010 7:47:01 PM
Ossie,

Along those same lines, is there a couple lines of code that will clear the 
contents on all of the rows below the last of row of contiguous data, even if 
the rows are hidden. 

In other words, a set of data is in A1:J100, and then there are about 20 
blank rows, then some more data starts in A120. I'd want the code to find the 
last row of the initial contiguous data (100), then delete everything in the 
spreadsheet below it even if some rows are hidden.

Thanks again!
Dani

"OssieMac" wrote:

> Hi Dani,
> 
> Here are 2 options. See the comments for when to use the option.
> 
> 'Option 1
> Sub DeleteRows1()
> 'When a specific column will always have data in last row.
> 
> Dim lastRow As Long
> 
> With Sheets("Sheet1")
>   'Can replace "A" with any column that
>   'will always have data in the last row.
>   lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
>   .Rows(lastRow - 1 & ":" & lastRow).Delete
> 
> End With
> 
> End Sub
> 
> 
> 'Option 2
> Sub DeleteRows2()
> 
> 'If it is not know which column
> 'will always have data in the last row.
> 
> Dim lastRow As Long
> 
> With Sheets("Sheet1")
>   
>   lastRow = .Cells _
>     .Find(What:="*", _
>     After:=.Cells(1, 1), _
>     LookIn:=xlFormulas, _
>     LookAt:=xlPart, _
>     SearchOrder:=xlByRows, _
>     SearchDirection:=xlPrevious, _
>     MatchCase:=False, _
>     SearchFormat:=False).Row
>     
>   .Rows(lastRow & ":" & lastRow - 1).Delete
>   
> End With
> 
> End Sub
> 
> -- 
> Regards,
> 
> OssieMac
> 
> 
0
Utf
3/11/2010 8:14:01 PM
Perhaps this code will do what you want...

Dim FirstBlankCell As Range
'....
'....
On Error Resume Next
Set FirstBlankCell = Worksheets("Sheet1").Range("A1:J100"). _
                            SpecialCells(xlCellTypeBlanks)
On Error GoTo 0
If Not FirstBlankCell Is Nothing Then
  Rows(FirstBlankCell(1).Row & ":100").ClearContents
End If

Note 1: Since you cannot Undo the deletions from a macro, you should
        first test this code out on a copy of your worksheet and not
        on your live data.

Note 2: Change my reference to Worksheets("Sheet1") to reflect the
        actual worksheet name you want to apply this code to.

-- 
Rick (MVP - Excel)


"Danielle" <Danielle@discussions.microsoft.com> wrote in message 
news:2BE5C77F-991F-4FCC-B845-F64A02F879CB@microsoft.com...
> Ossie,
>
> Along those same lines, is there a couple lines of code that will clear 
> the
> contents on all of the rows below the last of row of contiguous data, even 
> if
> the rows are hidden.
>
> In other words, a set of data is in A1:J100, and then there are about 20
> blank rows, then some more data starts in A120. I'd want the code to find 
> the
> last row of the initial contiguous data (100), then delete everything in 
> the
> spreadsheet below it even if some rows are hidden.
>
> Thanks again!
> Dani
>
> "OssieMac" wrote:
>
>> Hi Dani,
>>
>> Here are 2 options. See the comments for when to use the option.
>>
>> 'Option 1
>> Sub DeleteRows1()
>> 'When a specific column will always have data in last row.
>>
>> Dim lastRow As Long
>>
>> With Sheets("Sheet1")
>>   'Can replace "A" with any column that
>>   'will always have data in the last row.
>>   lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
>>   .Rows(lastRow - 1 & ":" & lastRow).Delete
>>
>> End With
>>
>> End Sub
>>
>>
>> 'Option 2
>> Sub DeleteRows2()
>>
>> 'If it is not know which column
>> 'will always have data in the last row.
>>
>> Dim lastRow As Long
>>
>> With Sheets("Sheet1")
>>
>>   lastRow = .Cells _
>>     .Find(What:="*", _
>>     After:=.Cells(1, 1), _
>>     LookIn:=xlFormulas, _
>>     LookAt:=xlPart, _
>>     SearchOrder:=xlByRows, _
>>     SearchDirection:=xlPrevious, _
>>     MatchCase:=False, _
>>     SearchFormat:=False).Row
>>
>>   .Rows(lastRow & ":" & lastRow - 1).Delete
>>
>> End With
>>
>> End Sub
>>
>> -- 
>> Regards,
>>
>> OssieMac
>>
>> 

0
Rick
3/11/2010 9:15:54 PM
Thanks Rick,

This works except that the range on the spreadsheet is different with each 
report so it doesn't always end on row 100. I need it to find where it ends, 
then do the delete. 

Any other thoughts?

"Rick Rothstein" wrote:

> Perhaps this code will do what you want...
> 
> Dim FirstBlankCell As Range
> '....
> '....
> On Error Resume Next
> Set FirstBlankCell = Worksheets("Sheet1").Range("A1:J100"). _
>                             SpecialCells(xlCellTypeBlanks)
> On Error GoTo 0
> If Not FirstBlankCell Is Nothing Then
>   Rows(FirstBlankCell(1).Row & ":100").ClearContents
> End If
> 
> Note 1: Since you cannot Undo the deletions from a macro, you should
>         first test this code out on a copy of your worksheet and not
>         on your live data.
> 
> Note 2: Change my reference to Worksheets("Sheet1") to reflect the
>         actual worksheet name you want to apply this code to.
> 
> -- 
> Rick (MVP - Excel)
> 
> 
> "Danielle" <Danielle@discussions.microsoft.com> wrote in message 
> news:2BE5C77F-991F-4FCC-B845-F64A02F879CB@microsoft.com...
> > Ossie,
> >
> > Along those same lines, is there a couple lines of code that will clear 
> > the
> > contents on all of the rows below the last of row of contiguous data, even 
> > if
> > the rows are hidden.
> >
> > In other words, a set of data is in A1:J100, and then there are about 20
> > blank rows, then some more data starts in A120. I'd want the code to find 
> > the
> > last row of the initial contiguous data (100), then delete everything in 
> > the
> > spreadsheet below it even if some rows are hidden.
> >
> > Thanks again!
> > Dani
> >
> > "OssieMac" wrote:
> >
> >> Hi Dani,
> >>
> >> Here are 2 options. See the comments for when to use the option.
> >>
> >> 'Option 1
> >> Sub DeleteRows1()
> >> 'When a specific column will always have data in last row.
> >>
> >> Dim lastRow As Long
> >>
> >> With Sheets("Sheet1")
> >>   'Can replace "A" with any column that
> >>   'will always have data in the last row.
> >>   lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
> >>   .Rows(lastRow - 1 & ":" & lastRow).Delete
> >>
> >> End With
> >>
> >> End Sub
> >>
> >>
> >> 'Option 2
> >> Sub DeleteRows2()
> >>
> >> 'If it is not know which column
> >> 'will always have data in the last row.
> >>
> >> Dim lastRow As Long
> >>
> >> With Sheets("Sheet1")
> >>
> >>   lastRow = .Cells _
> >>     .Find(What:="*", _
> >>     After:=.Cells(1, 1), _
> >>     LookIn:=xlFormulas, _
> >>     LookAt:=xlPart, _
> >>     SearchOrder:=xlByRows, _
> >>     SearchDirection:=xlPrevious, _
> >>     MatchCase:=False, _
> >>     SearchFormat:=False).Row
> >>
> >>   .Rows(lastRow & ":" & lastRow - 1).Delete
> >>
> >> End With
> >>
> >> End Sub
> >>
> >> -- 
> >> Regards,
> >>
> >> OssieMac
> >>
> >> 
> 
> .
> 
0
Utf
3/12/2010 7:23:01 PM
Okay, try this code instead...

Dim FirstBlankCell As Range, LastRow As Long
'....
'....
On Error Resume Next
LastRow = ActiveSheet.Cells.Find(What:="*", SearchOrder:=xlRows, _
           SearchDirection:=xlPrevious, LookIn:=xlFormulas).Row
Set FirstBlankCell = Worksheets("Sheet1").Range("A:J"). _
                        SpecialCells(xlCellTypeBlanks)
On Error GoTo 0
If Not FirstBlankCell Is Nothing Then
  Rows(FirstBlankCell(1).Row & ":" & LastRow).ClearContents
End If

-- 
Rick (MVP - Excel)


"Danielle" <Danielle@discussions.microsoft.com> wrote in message 
news:A7E7F034-66CD-45A2-BD28-0E0232FB063B@microsoft.com...
> Thanks Rick,
>
> This works except that the range on the spreadsheet is different with each
> report so it doesn't always end on row 100. I need it to find where it 
> ends,
> then do the delete.
>
> Any other thoughts?
>
> "Rick Rothstein" wrote:
>
>> Perhaps this code will do what you want...
>>
>> Dim FirstBlankCell As Range
>> '....
>> '....
>> On Error Resume Next
>> Set FirstBlankCell = Worksheets("Sheet1").Range("A1:J100"). _
>>                             SpecialCells(xlCellTypeBlanks)
>> On Error GoTo 0
>> If Not FirstBlankCell Is Nothing Then
>>   Rows(FirstBlankCell(1).Row & ":100").ClearContents
>> End If
>>
>> Note 1: Since you cannot Undo the deletions from a macro, you should
>>         first test this code out on a copy of your worksheet and not
>>         on your live data.
>>
>> Note 2: Change my reference to Worksheets("Sheet1") to reflect the
>>         actual worksheet name you want to apply this code to.
>>
>> -- 
>> Rick (MVP - Excel)
>>
>>
>> "Danielle" <Danielle@discussions.microsoft.com> wrote in message
>> news:2BE5C77F-991F-4FCC-B845-F64A02F879CB@microsoft.com...
>> > Ossie,
>> >
>> > Along those same lines, is there a couple lines of code that will clear
>> > the
>> > contents on all of the rows below the last of row of contiguous data, 
>> > even
>> > if
>> > the rows are hidden.
>> >
>> > In other words, a set of data is in A1:J100, and then there are about 
>> > 20
>> > blank rows, then some more data starts in A120. I'd want the code to 
>> > find
>> > the
>> > last row of the initial contiguous data (100), then delete everything 
>> > in
>> > the
>> > spreadsheet below it even if some rows are hidden.
>> >
>> > Thanks again!
>> > Dani
>> >
>> > "OssieMac" wrote:
>> >
>> >> Hi Dani,
>> >>
>> >> Here are 2 options. See the comments for when to use the option.
>> >>
>> >> 'Option 1
>> >> Sub DeleteRows1()
>> >> 'When a specific column will always have data in last row.
>> >>
>> >> Dim lastRow As Long
>> >>
>> >> With Sheets("Sheet1")
>> >>   'Can replace "A" with any column that
>> >>   'will always have data in the last row.
>> >>   lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
>> >>   .Rows(lastRow - 1 & ":" & lastRow).Delete
>> >>
>> >> End With
>> >>
>> >> End Sub
>> >>
>> >>
>> >> 'Option 2
>> >> Sub DeleteRows2()
>> >>
>> >> 'If it is not know which column
>> >> 'will always have data in the last row.
>> >>
>> >> Dim lastRow As Long
>> >>
>> >> With Sheets("Sheet1")
>> >>
>> >>   lastRow = .Cells _
>> >>     .Find(What:="*", _
>> >>     After:=.Cells(1, 1), _
>> >>     LookIn:=xlFormulas, _
>> >>     LookAt:=xlPart, _
>> >>     SearchOrder:=xlByRows, _
>> >>     SearchDirection:=xlPrevious, _
>> >>     MatchCase:=False, _
>> >>     SearchFormat:=False).Row
>> >>
>> >>   .Rows(lastRow & ":" & lastRow - 1).Delete
>> >>
>> >> End With
>> >>
>> >> End Sub
>> >>
>> >> -- 
>> >> Regards,
>> >>
>> >> OssieMac
>> >>
>> >>
>>
>> .
>> 

0
Rick
3/12/2010 8:29:27 PM
Reply:

Similar Artilces:

How to combine two win32 projects into one
hai, i have developed two win32 modules seperately in VC++ . these are for conversion of media. But i don't know how to combine bothe these. can anybody tell.....what are the steps to do that one?? i have just added one source file and header file of one module into another one respectively. but at the time of compiling it shows the following error. C:\Documents and Settings\balu\Desktop\DVDSRC\disk space\NGDiskSpaceMonitor.cpp(340): error C2665: 'AfxBeginThread' : none of the 2 overloads can convert parameter 1 fr...

Compare and Copy/Paste b/w Two Workbooks
I have this macro that compares values in two columns (A & M) in the same sheet and if there is a match, it takes the value in the adjacent column (N) and pastes it into ColumnE. Sub MatchAandM() Dim Lrow As Long Dim RowCount As Long Dim xRng As Range Lrow = Range("A" & Rows.Count).End(xlUp).Row For RowCount = 2 To Lrow FindVal = Range("A" & RowCount) Set xRng = Columns("M:M").Find(What:=FindVal, _ LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False) If Not xRng Is Nothing Then xRng.Offset(0, 1).Copy xRng.Offset(0,...

Can't clear print queue
How do I clear the print queue? I've tried "net stop spooler" and deleted all in /WINDOWS/System32/spool/PRINTERS, to no avail. The OS is XP Home SP3. The printer is HP psc 1200. "Herzl Regev" <HerzlRegev@discussions.microsoft.com> wrote in message news:03C416F4-BE1D-4CE5-A865-4F5D0FD3EC57@microsoft.com... > How do I clear the print queue? > I've tried "net stop spooler" and deleted all in > /WINDOWS/System32/spool/PRINTERS, to no avail. > > The OS is XP Home SP3. The printer is HP psc 1200. What happened when you...

NullReferenceException after Data Migration.
Hi, I already posted this question here but not solve my problem. After a simple test migration some pages of WEB GUI show this error: NullReferenceException: Object reference not set to an instance of an object. I migrate one appointment and one activityparty with participation mask of organizer for a user. After the migration I can see the appointment on CRM in the activity list, the calendar day view, the calendar month view, but in the calendar week view I get this error: [NullReferenceException: Object reference not set to an instance of an object.] Microsoft.Crm.CalendarUtil.BuildCo...

Tricking CRM 3 into running two languages
Hello, Having had English version of CRM 3, we got it installed, on Win 2003 SP1, SQL Server 2000. We have now received Russian version of the product. Since we shall be deploying Russian version for local clients and not all of our internal staff are fluent in English, the boss tells to replace English installation with Russian. Okay to that, I thought... only I'd really have preferred it with dual language support - as I find it easier to understand English interface and online documentation, if it were somehow possible at all. I also administer WSUS and it's magic there - ju...

Match a value to multple criteria both in rows and columns
In my source data sheet, starting in A1, I have the following information: Property Dept Total Reported Wk 1 Wk 2 Wk 3 Wk4 ABC Finance 7 1 2 4 0 ABC Sales 2 1 1 0 0 XYX Finance 85 22 26 23 14 I then have one worksheet for each property (property name in A1), with the departments vertically listed, starting in column C: Department Total Reported Wk 1 Wk 2 Wk 3 Wk 4 HR Finance Sales I need for cells...

Clear
Due to a malformed e-mail message or other malformed item in the information store, the Microsoft Exchange Information Store service may crash periodically. It happens when a staff try to move his mail from exchange mail box to his personal folders. Now I have to restart the services - Microsoft Exchange Information Store every 1 min. I attempt to clean his mail box (not delete) in the exchange server such that to fix this problem. Can you perform an "online backup" of the information store? If this is successful, then the problem is probably related to the structure of ...

How do I set end-of-range to #rows from beginning
Specifically, I am creating a chart that will have a breadth (# data points) based on a variable (value of a cell). Start point is always the same. For instance, a table is created based on a formula. The chart should stop when values in the table reach a certain value, but this could be 20 or 200 datapoints (20-200 rows). I know how many datapoints should be included, but not how to terminate the graph based on this number. I would prefer to do this without VBA, as I am not particularly proficient, but will if the only way. Hi, It would be nice to see some sample data, with a...

Re: Where should be the best place to stored Applcation Data Files?
"Giovanni Dicanio" <gdicanio@_NOSPAM_email_DOT_it> ha scritto nel messaggio news:... > BTW: this post by John Robbins is very interesting reading: > http://www.wintellect.com/CS/blogs/jrobbins/default.aspx copy-and-pasto - the correct link is this: http://www.wintellect.com/CS/blogs/jrobbins/archive/2008/07/10/in-defense-of-vista-and-the-challenges-facing-windows-7.aspx Giovanni And then there's this Vista security discussion from last Friday (cool date, 8/8/8): http://it.slashdot.org/article.pl?sid=08/08/08/1155208 -GB On Fri, 8 Aug 2008 18:50:23 +0200, ...

View two page spread?
Hello, I clicked on View>two page spread because I want to view both sides of a single sheet at the same time. However, the view does not change even though there is a check mark next to the command. Shouldn't I be able to view both sides of a sheet at once? TIA, Catt Catt <Catt@discussions.microsoft.com> was very recently heard to utter: > I clicked on View>two page spread because I want to view both sides > of a single sheet at the same time. However, the view does not > change even though there is a check mark next to the command. > Shouldn't I be able t...

clear the list og "open other users folder"
Hi When you use the open an other user folder, it could be a calendar, the persons name and folder is put into a list under the menu File/Open. I would like to clear that numbered list of folders, does anyone know how to do that? thanks, Rikke ...

Query from RMS data
I am trying to create a Pivot table in Excel, using Microsoft Query linking to RMS SQL database. SQL stores the date as Date/Time. It becomes impossible to group in Excel using Pivot Table, by date/time. I want to create a record in Microsoft query using the date field, but convert to Date only as dd/mm/yyyy and retain the format as Date and not as Text. I have tried Left(Date,11) and Convert(VARCHAR,1,101), none of them work. Could someone help me with the correct syntax. Microsoft query does not recognize MID. Thanks I've had a similar problem. The solution I used was to convert ...

Columns and rows are numbered
For some really strange reason lately, when I'm working in Excel both my rows and columns are numbered 1,2,3,4,5....This makes it really difficult to deal with . How do I get excel to stop doing this I want my columns to go back to being lettered A,b,c,d, etc....Anyone else ever have this happen or know how to fix it. It's annoying. Thanks Tools - options - general- R1C1 style- UNTICK. ;) >-----Original Message----- >For some really strange reason lately, when I'm working in Excel both my rows >and columns are numbered 1,2,3,4,5....This makes it really difficu...

times part of cell content
I got a column like this: 11/40 35/10 101/100 250/40 I need to times the number before the / with 2 in each row so I get 22/40 70/10 202/100 500/40 Anyone know how I can do this? Thanks Mike "Mike Mike" <mm@mitechhostREMOVE.com> wrote in message news:1rxVf.96540$Fw6.43301@tornado.tampabay.rr.com... >I got a column like this: > 11/40 > 35/10 > 101/100 > 250/40 > > I need to times the number before the / with 2 in each row so I get > 22/40 > 70/10 > 202/100 > 500/40 > > Anyone know how I can do this? > > Thanks > Mike Mi...

Getting Data Validation to execute when using Paste Special... Val
I know that when data is normally pasted into a cell that contains Data Validation, the Data Validation rule is deleted/cleared. However, if I use Paste Special... Values, for example, the Data Validation rule is preserved, although it does not execute. It's only if I subsequently edit the cell (after having performed Paste Special... Values) that the Data Validation rule executes. The code below causes copied data to be pasted only as a value (thereby preserving the Data Validation rule). What I can't seem to figure out is how to cause the Data Validation rule to b...

Formula to enter data of one cell based on greatest amount of anot
Hello! I am looking for a formula that will search an array for the greatest number, then enter data from the corresponding cell of a different array. Try this: =3DINDEX(D:D,MATCH(MAX(A:A),A:A,0)) assuming you are looking in column A for the largest number and want the corresponding data returned from column D. If you have multiple maxima, then this will respond to the first of those. Hope this helps. Pete On Jan 13, 1:07=A0am, classroomlaw <classroom...@discussions.microsoft.com> wrote: > Hello! =A0I am looking for a formula that will search an array for the...

Row formatting based on cell content
I tried searching for this but no luck...what i have is a price lis that i copy and paste to my excel document. BLUETOOTH DBT120 D-LINK Bluetooth USB Adapter DCF650BT D-LINK Bluetooth PDA Compact Flash CABLE CABLE - SURGE FILTER SF001NP SURGE FILTER PC POWER CABLE CABLE - IDE HDD/CD ROM AUDCAB AUDIO CABLE - BLACK TO BLACK IDECAB IDE DATA CABLE (HDD/CD ROM) - 3 drop IDECAB133 IDE DATA CABLE -ULTRA ATA 133 (HDD) CABLE - NETWORK CABUTP005 0.5 METRE UTP RJ45 LEVEL 5 NETWORK CABLE CABUTP01 1 METRE UTP RJ45 LEVEL 5 NETWORK CABLE CABUTP02 2 METRE UTP RJ45 LEVEL 5 NETWORK CABLE This would...

count cells with multiple criteria and two rows
I have a spreadsheet with 4 different worksheets. I basically want to sum the information from the 4 worksheets on a separate worksheet. From worksheet1, I want to count the cells that contain the letter "D" from B2:B30, but only count those that have a "D" if "CO007" is in E2:30......same criteria from worksheet2, 3 and 4. I started with =count(if(worksheet1!B2:B30,"*d")+(worksheet1!E2:E30,"CO007") + count(if(worksehet2!B2:B30,"*d") etc. but that didn't work. Please help. something like =sumproduct((b2:b22="d...

Clear data from pivot table
Hello all. Usually when building a pivot table report I throw the data sets in the pivot table a few times to find the best way to show data. So I wonder - is there a way to quickly erase all the data sets so the pivot table is again empty as just being created (so I do not have to select each data set and remove it? Macro or some other trick? The closest I got to the answer was the idea to just create another pivot table from the original data=85but it would be so much easier to click a button and all the data is cleared from the pivot table. Thank you as always people! In Excel 2007 ther...

Show phrase when no data for a report
I have followed several threads on this subject which suggest using the IIf statement in a new text box. Some suggest the IIf should be on a particular field and some suggest on the report itself. I am unable to get the required result and wonder when using a text box for the control source (to enter the IIF statement in to), where it should be placed on the report. Specifically my report is named "DR Diving Record Rpt" and a field from it is "ship" I have tried adding a text box with the following =IIf([DR Diving Record Rpt].[Report].[HasData], Null, &quo...

using data with an xsd to send a string of xml to a web service
I know I wrote before a week ago when I knew even less than now but I am getting better please anyone give me a clue or an example. Am I completely off track? I have a datarow in a table with the fields to map to an xsd. Then i call a web service upload method that wants the xml strongly typed to the xsd in a string. so I have generated a class from a xsd newcust.cs in .net 1.1 c# to start with just to see what it means to use an xsd with a dataset to create an xml document for starter but I get the error at the end I know I am new but I am running out of options to teach myself f...

clear 1099 history
any one have any scripts to clear 1099 history by vendor or vendor class? Current Year UPDATE PM00201 SET TEN99AYTD = 0 WHERE VENDORID = 'xxxxxxxx' Life to Date UPDATE PM00201 SET TEN99ALIF = 0 WHERE VENDORID = 'xxxxxxxx' UPDATE PM00202 SET TEN99ALIF = 0 WHERE VENDORID = 'xxxxxxxx' Last Year UPDATE PM00201 SET TEN99ALYR = 0 WHERE VENDORID = 'xxxxxxxx' PM00201 is the PM Vendor Master Summary, PM00202 is the PM Vendor Master Period Summary. Life to Date amounts are stored in both tables. Current Year and Last Ye...

How to calculate diffrence between two dates
In Access expressions, how can I calculate the difference between two dates in years, months and days? "Vianny" <Vianny@discussions.microsoft.com> wrote in message news:9C90CB32-1C04-4124-8FC7-583D62004906@microsoft.com... > In Access expressions, how can I calculate the difference between two > dates > in years, months and days? Look up the DateDiff function in help. It does precisely what you want. Unless something has changed in Access 2007, DateDiff will return one time interval (years OR months OR days), but not a combination. Here is a link...

Filtering data within macros
I have a spread sheet with 6 coloums of data such as name,month,job. have filters so i can see how many jobs are done in a month or how man jobs one person is doing. I would like to be able to write a macr which works out most of the combinations of filters and outputs it t a seperate sheet. There are lots of different options for each coloum tho! Is there any quick ways i can do this other than reseting th filters everytime for every combination? hope i have explained this ok....its even confusing me!! Cheers Robert -- Message posted from http://www.ExcelForum.com This may be helpful ...

Clear outlook and reset for new user
I'm at a new job. I got the fired guy's work station. How do I clear everything out of MS Outlook and reset it for my own use? Start with a new mail profile; Control Panel-> Mail-> Show Profiles For details see; http://www.howto-outlook.com/faq/newprofile.htm Starting with a new user account would also clear the rest of his settings for the entire Windows environment and configured applications. -- Robert Sparnaaij [MVP-Outlook] Coauthor, Configuring Microsoft Outlook 2003 http://www.howto-outlook.com/ Outlook FAQ, HowTo, Downloads, Add-Ins and more http:/...