Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > microsoft.public.excel.programming > #109639

Re: Compare 2 Lists and Take an Action Q

From Claus Busch <claus_busch@t-online.de>
Newsgroups microsoft.public.excel.programming
Subject Re: Compare 2 Lists and Take an Action Q
Date 2016-11-22 13:26 +0100
Organization A noiseless patient Spider
Message-ID <o11dh3$3sq$1@dont-email.me> (permalink)
References (16 earlier) <e0fbc4aa-1f08-4f68-b1d0-edb3ef50366e@googlegroups.com> <o11293$tdp$1@dont-email.me> <90b434a5-518d-4365-ba1b-1bb32a61b9ba@googlegroups.com> <o114o4$5p7$1@dont-email.me> <93e5c7b7-42b5-4db8-a342-ba733f729c69@googlegroups.com>

Show all headers | View raw

Hi Sean,

Am Tue, 22 Nov 2016 03:28:25 -0800 (PST) schrieb seanryanie@yahoo.co.uk:

> Just a few things to tidy up now. When I'm setting up a new Reconciliation, how do I delete the rows on the 'Report' sheet that the Filters are copied to? 
> 
> I want to do this just to have a neat layout at start point, as the Filter Code will insert if not enough Rows anyway

to delete the data and reset number of rows back in first sector to 14
and in second sector to 7 try:

Sub DeleteRows()
Dim First As Range, Last As Range, Start As Range
Dim Diff1 As Long, Diff2 As Long

With Sheets("Report")
    Set Start = .Range("A14")
    Set First = .Range("A:A").Find("Payments /", lookat:=xlPart)
    Set Last = .Range("A:A").Find("Adjusted Variance", lookat:=xlWhole)
    Diff1 = First.Row - 14 - 5
    Diff2 = Last.Row - First.Row - 4
    Start.Resize(Diff1, 8).ClearContents
    First.Offset(2, 0).Resize(Diff2, 8).ClearContents
    If Diff1 > 14 Then .Rows(Start.Row).Resize(Diff1 - 12).Delete
    If Diff2 > 7 Then .Rows(First.Row + 2).Resize(Diff2 - 6).Delete
End With
End Sub


Regards
Claus B.
-- 
Windows10
Office 2016

Back to microsoft.public.excel.programming | Previous | Next — Previous in thread | Next in thread | Find similar | Unroll thread

Thread

Compare 2 Lists and Take an Action Q seanryanie@yahoo.co.uk - 2016-11-18 11:17 -0800
  Re: Compare 2 Lists and Take an Action Q Claus Busch <claus_busch@t-online.de> - 2016-11-18 20:38 +0100
    Re: Compare 2 Lists and Take an Action Q GS <gs@v.invalid> - 2016-11-18 14:46 -0500
      Re: Compare 2 Lists and Take an Action Q Claus Busch <claus_busch@t-online.de> - 2016-11-18 21:07 +0100
        Re: Compare 2 Lists and Take an Action Q seanryanie@yahoo.co.uk - 2016-11-18 12:42 -0800
          Re: Compare 2 Lists and Take an Action Q Claus Busch <claus_busch@t-online.de> - 2016-11-18 21:51 +0100
            Re: Compare 2 Lists and Take an Action Q GS <gs@v.invalid> - 2016-11-18 16:04 -0500
          Re: Compare 2 Lists and Take an Action Q GS <gs@v.invalid> - 2016-11-18 16:05 -0500
            Re: Compare 2 Lists and Take an Action Q seanryanie@yahoo.co.uk - 2016-11-18 13:26 -0800
              Re: Compare 2 Lists and Take an Action Q seanryanie@yahoo.co.uk - 2016-11-21 08:09 -0800
                Re: Compare 2 Lists and Take an Action Q Claus Busch <claus_busch@t-online.de> - 2016-11-21 17:22 +0100
                Re: Compare 2 Lists and Take an Action Q seanryanie@yahoo.co.uk - 2016-11-21 08:33 -0800
                Re: Compare 2 Lists and Take an Action Q seanryanie@yahoo.co.uk - 2016-11-21 09:19 -0800
                Re: Compare 2 Lists and Take an Action Q Claus Busch <claus_busch@t-online.de> - 2016-11-21 19:05 +0100
                Re: Compare 2 Lists and Take an Action Q seanryanie@yahoo.co.uk - 2016-11-21 11:39 -0800
                Re: Compare 2 Lists and Take an Action Q Claus Busch <claus_busch@t-online.de> - 2016-11-22 07:47 +0100
                Re: Compare 2 Lists and Take an Action Q seanryanie@yahoo.co.uk - 2016-11-21 22:58 -0800
                Re: Compare 2 Lists and Take an Action Q Claus Busch <claus_busch@t-online.de> - 2016-11-22 08:04 +0100
                Re: Compare 2 Lists and Take an Action Q seanryanie@yahoo.co.uk - 2016-11-22 00:40 -0800
                Re: Compare 2 Lists and Take an Action Q Claus Busch <claus_busch@t-online.de> - 2016-11-22 10:14 +0100
                Re: Compare 2 Lists and Take an Action Q seanryanie@yahoo.co.uk - 2016-11-22 01:35 -0800
                Re: Compare 2 Lists and Take an Action Q Claus Busch <claus_busch@t-online.de> - 2016-11-22 10:56 +0100
                Re: Compare 2 Lists and Take an Action Q seanryanie@yahoo.co.uk - 2016-11-22 03:28 -0800
                Re: Compare 2 Lists and Take an Action Q Claus Busch <claus_busch@t-online.de> - 2016-11-22 13:26 +0100
                Re: Compare 2 Lists and Take an Action Q Claus Busch <claus_busch@t-online.de> - 2016-11-22 13:39 +0100
                Re: Compare 2 Lists and Take an Action Q seanryanie@yahoo.co.uk - 2016-11-22 05:14 -0800
                Re: Compare 2 Lists and Take an Action Q Claus Busch <claus_busch@t-online.de> - 2016-11-22 14:29 +0100
                Re: Compare 2 Lists and Take an Action Q Claus Busch <claus_busch@t-online.de> - 2016-11-22 14:52 +0100
                Re: Compare 2 Lists and Take an Action Q seanryanie@yahoo.co.uk - 2016-11-22 11:20 -0800
                Re: Compare 2 Lists and Take an Action Q seanryanie@yahoo.co.uk - 2016-11-22 12:19 -0800
                Re: Compare 2 Lists and Take an Action Q Claus Busch <claus_busch@t-online.de> - 2016-11-22 22:36 +0100
  Re: Compare 2 Lists and Take an Action Q bulong <bulong@gmail.com> - 2016-11-19 09:29 +0000

csiph-web