Conditional Formatting 4 Columns

I have a workbook with 4 columns containing lists of names from 4 different
workbooks pasted as links to a name list in each of those workbooks, one
list per workbook. I'm trying to compare those lists using Conditional
Formatting. Ideally each column's results will match, but if they don't, I
want to know which ones don't. If I select the entire range, is there a
formula I can use to turn non-matches red?

--
David

Since you have 4 different lists, how about an alternative?

Create a new sheet (call it sheet2)
Put Name in A1
Copy the 4 lists into column A of this new sheet (one under the other)

Then select that range (A1:A###)
data|filter|advanced filter
Copy to another location
List range: (should be entered (a1:A###)
copy to: B1
Check Unique records only box

Now you have a list of unique names in column B.
Delete column A (we're done with it).

In B1, put: On List 1
In C1, put: On List 2
in D1, put: On List 3
in E1, put: On list 4

In B2, put this formula:
=isnumber(match(a2,sheet1!a:a,0))

In C2, put this:
=isnumber(match(a2,sheet1!b:b,0))

In D2:
=isnumber(match(a2,sheet1!c:c,0))

In E2:
=isnumber(match(a2,sheet1!D:D,0))

And drag down as far as column A extends.

Now select columns A:E and do Data|Filter|Autofilter.

You can filter to show the Falses in any column to see where its missing.



David wrote:
>
> I have a workbook with 4 columns containing lists of names from 4 different
> workbooks pasted as links to a name list in each of those workbooks, one
> list per workbook. I'm trying to compare those lists using Conditional
> Formatting. Ideally each column's results will match, but if they don't, I
> want to know which ones don't. If I select the entire range, is there a
> formula I can use to turn non-matches red?
>
> --
> David

--

Dave Peterson

Dave Peterson wrote

> Since you have 4 different lists, how about an alternative?
>
> Create a new sheet (call it sheet2)
> Put Name in A1
> Copy the 4 lists into column A of this new sheet (one under the other)
>
> Then select that range (A1:A###)
> data|filter|advanced filter
> Copy to another location
> List range: (should be entered (a1:A###)
> copy to: B1
> Check Unique records only box
>
> Now you have a list of unique names in column B.
> Delete column A (we're done with it).
>
> In B1, put: On List 1
> In C1, put: On List 2
> in D1, put: On List 3
> in E1, put: On list 4
>
> In B2, put this formula:
> =isnumber(match(a2,sheet1!a:a,0))
>
> In C2, put this:
> =isnumber(match(a2,sheet1!b:b,0))
>
> In D2:
> =isnumber(match(a2,sheet1!c:c,0))
>
> In E2:
> =isnumber(match(a2,sheet1!D:D,0))
>
> And drag down as far as column A extends.
>
> Now select columns A:E and do Data|Filter|Autofilter.
>
> You can filter to show the Falses in any column to see where its
> missing.

Well, things broke when I tried to copy the 2nd list (and presumably
would do the same with 3rd and 4th) with a #REF to cells from that 2nd
list, which references cells from a 2nd external file. I didn't pursue
things any further. Remember these lists are "built" as a result of links
to external files. If I pasted values, wouldn't I have to repeat your
proposed process each time one or more lists change (quite often)?
Basically this is a test file to insure I have not missed adding/deleting
a name from all 4 files. I can immediately see if things don't match when
length of lists differ, but then have to scan visually to see what name
starts the difference. Additionally each source range is from a different
range in each of the source workbooks.

Here's what I want:
If name is in A, but not in B or C or D, I want to know
If name is in B, but not in A or C or D, I want to know
If name is in C, but not in A or B or D, I want to know
If name is in D, but not in A or B or C, I want to know

Am I asking for 4 conditions, thereby exceeding CF's limit of 3? If so,
I'll accept 3 out of 4 <g>.

--
David

Personally, I find Format|Conditional formatting very pretty--but pretty much
useless. You can't (easily) count the missing items; you can't filter by that
conditional formatting color.

If I had to do it over and over and over, I'd record a macro when I did those
steps (including the paste|special|Values). Then just rerun that whenever I
needed the info.

This seemed to work ok for me:

Option Explicit
Sub testme()
Dim CurWks As Worksheet
Dim NewWks As Worksheet
Dim iCol As Long
Dim DestCell As Range
Dim MaxCols As Long
Dim LastRow As Long

Set CurWks = Worksheets("sheet1")

Set NewWks = Worksheets.Add
Set DestCell = NewWks.Range("a2")

With CurWks
MaxCols = .Cells(1, .Columns.Count).End(xlToLeft).Column
For iCol = 1 To MaxCols
.Range(.Cells(2, iCol), .Cells(.Rows.Count, iCol).End(xlUp)).Copy
DestCell.PasteSpecial Paste:=xlPasteValues
With NewWks
Set DestCell = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0)
End With
Next iCol
End With

With NewWks
.Range("a1").Value = "Name"
.Range("A1", .Cells(.Rows.Count, "A").End(xlUp)).AdvancedFilter _
Action:=xlFilterCopy, copytorange:=.Range("B1"), unique:=True

.Range("a1").EntireColumn.Delete

.Range("a1").EntireColumn.Sort key1:=.Range("a1"), _
order1:=xlAscending, header:=xlYes

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

For iCol = 1 To MaxCols
.Cells(1, iCol + 1).Value = "On List#" & iCol
With .Range(.Cells(2, iCol + 1), .Cells(LastRow, iCol + 1))
.Formula = "=isnumber(match(A2," _
& CurWks.Columns(iCol).Address(external:=True) & ",0))"
.Value = .Value
.Replace what:="True", replacement:="", _
lookat:=xlWhole, MatchCase:=False
.Replace what:="False", replacement:="No", _
lookat:=xlWhole, MatchCase:=False
.HorizontalAlignment = xlCenter
End With
Next iCol

.Cells(1, MaxCols + 2).Value = "Count Of No's"

With .Range(.Cells(2, MaxCols + 2), .Cells(LastRow, MaxCols + 2))
.Formula = "=countif(B2:" & _
.Parent.Cells(2, MaxCols + 1).Address(0, 0) & ",""no"")"
.Value = .Value
.HorizontalAlignment = xlCenter
End With

.Range("a1", .Cells(LastRow, MaxCols + 2)).AutoFilter

Application.Goto .Range("a1"), Scroll:=True
.Range("b2").Select
ActiveWindow.FreezePanes = True

.UsedRange.Columns.AutoFit

Set DestCell = .UsedRange 'try to reset last used cell

End With

End Sub

David wrote:
>
> Dave Peterson wrote
>
> > Since you have 4 different lists, how about an alternative?
> >
> > Create a new sheet (call it sheet2)
> > Put Name in A1
> > Copy the 4 lists into column A of this new sheet (one under the other)
> >
> > Then select that range (A1:A###)
> > data|filter|advanced filter
> > Copy to another location
> > List range: (should be entered (a1:A###)
> > copy to: B1
> > Check Unique records only box
> >
> > Now you have a list of unique names in column B.
> > Delete column A (we're done with it).
> >
> > In B1, put: On List 1
> > In C1, put: On List 2
> > in D1, put: On List 3
> > in E1, put: On list 4
> >
> > In B2, put this formula:
> > =isnumber(match(a2,sheet1!a:a,0))
> >
> > In C2, put this:
> > =isnumber(match(a2,sheet1!b:b,0))
> >
> > In D2:
> > =isnumber(match(a2,sheet1!c:c,0))
> >
> > In E2:
> > =isnumber(match(a2,sheet1!D:D,0))
> >
> > And drag down as far as column A extends.
> >
> > Now select columns A:E and do Data|Filter|Autofilter.
> >
> > You can filter to show the Falses in any column to see where its
> > missing.
>
> Well, things broke when I tried to copy the 2nd list (and presumably
> would do the same with 3rd and 4th) with a #REF to cells from that 2nd
> list, which references cells from a 2nd external file. I didn't pursue
> things any further. Remember these lists are "built" as a result of links
> to external files. If I pasted values, wouldn't I have to repeat your
> proposed process each time one or more lists change (quite often)?
> Basically this is a test file to insure I have not missed adding/deleting
> a name from all 4 files. I can immediately see if things don't match when
> length of lists differ, but then have to scan visually to see what name
> starts the difference. Additionally each source range is from a different
> range in each of the source workbooks.
>
> Here's what I want:
> If name is in A, but not in B or C or D, I want to know
> If name is in B, but not in A or C or D, I want to know
> If name is in C, but not in A or B or D, I want to know
> If name is in D, but not in A or B or C, I want to know
>
> Am I asking for 4 conditions, thereby exceeding CF's limit of 3? If so,
> I'll accept 3 out of 4 <g>.
>
> --
> David

--

Dave Peterson

ps.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

David wrote:
>
> Dave Peterson wrote
>
> > Since you have 4 different lists, how about an alternative?
> >
> > Create a new sheet (call it sheet2)
> > Put Name in A1
> > Copy the 4 lists into column A of this new sheet (one under the other)
> >
> > Then select that range (A1:A###)
> > data|filter|advanced filter
> > Copy to another location
> > List range: (should be entered (a1:A###)
> > copy to: B1
> > Check Unique records only box
> >
> > Now you have a list of unique names in column B.
> > Delete column A (we're done with it).
> >
> > In B1, put: On List 1
> > In C1, put: On List 2
> > in D1, put: On List 3
> > in E1, put: On list 4
> >
> > In B2, put this formula:
> > =isnumber(match(a2,sheet1!a:a,0))
> >
> > In C2, put this:
> > =isnumber(match(a2,sheet1!b:b,0))
> >
> > In D2:
> > =isnumber(match(a2,sheet1!c:c,0))
> >
> > In E2:
> > =isnumber(match(a2,sheet1!D:D,0))
> >
> > And drag down as far as column A extends.
> >
> > Now select columns A:E and do Data|Filter|Autofilter.
> >
> > You can filter to show the Falses in any column to see where its
> > missing.
>
> Well, things broke when I tried to copy the 2nd list (and presumably
> would do the same with 3rd and 4th) with a #REF to cells from that 2nd
> list, which references cells from a 2nd external file. I didn't pursue
> things any further. Remember these lists are "built" as a result of links
> to external files. If I pasted values, wouldn't I have to repeat your
> proposed process each time one or more lists change (quite often)?
> Basically this is a test file to insure I have not missed adding/deleting
> a name from all 4 files. I can immediately see if things don't match when
> length of lists differ, but then have to scan visually to see what name
> starts the difference. Additionally each source range is from a different
> range in each of the source workbooks.
>
> Here's what I want:
> If name is in A, but not in B or C or D, I want to know
> If name is in B, but not in A or C or D, I want to know
> If name is in C, but not in A or B or D, I want to know
> If name is in D, but not in A or B or C, I want to know
>
> Am I asking for 4 conditions, thereby exceeding CF's limit of 3? If so,
> I'll accept 3 out of 4 <g>.
>
> --
> David

--

Dave Peterson

Thanks very much for this code. One small detail I've tried to fiddle
with and can't get around. I'm sure it's simple. My lists on Sheet1 each
start in Row2, with Row1 being headers. How to adjust?

--
David

Dave Peterson wrote

> Personally, I find Format|Conditional formatting very pretty--but
> pretty much useless. You can't (easily) count the missing items; you
> can't filter by that conditional formatting color.
>
> If I had to do it over and over and over, I'd record a macro when I
> did those steps (including the paste|special|Values). Then just rerun
> that whenever I needed the info.
>
> This seemed to work ok for me:
>
> Option Explicit
> Sub testme()
> Dim CurWks As Worksheet
> Dim NewWks As Worksheet
> Dim iCol As Long
> Dim DestCell As Range
> Dim MaxCols As Long
> Dim LastRow As Long
>
> Set CurWks = Worksheets("sheet1")
>
> Set NewWks = Worksheets.Add
> Set DestCell = NewWks.Range("a2")
>
> With CurWks
> MaxCols = .Cells(1, .Columns.Count).End(xlToLeft).Column
> For iCol = 1 To MaxCols
> .Range(.Cells(2, iCol), .Cells(.Rows.Count,
> iCol).End(xlUp)).Copy DestCell.PasteSpecial
> Paste:=xlPasteValues With NewWks
> Set DestCell = .Cells(.Rows.Count,
> "A").End(xlUp).Offset(1, 0)
> End With
> Next iCol
> End With
>
> With NewWks
> .Range("a1").Value = "Name"
> .Range("A1", .Cells(.Rows.Count,
> "A").End(xlUp)).AdvancedFilter _
> Action:=xlFilterCopy, copytorange:=.Range("B1"),
> unique:=True
>
> .Range("a1").EntireColumn.Delete
>
> .Range("a1").EntireColumn.Sort key1:=.Range("a1"), _
> order1:=xlAscending,
> header:=xlYes
>
> LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
>
> For iCol = 1 To MaxCols
> .Cells(1, iCol + 1).Value = "On List#" & iCol
> With .Range(.Cells(2, iCol + 1), .Cells(LastRow, iCol +
> 1))
> .Formula = "=isnumber(match(A2," _
> & CurWks.Columns(iCol).Address(external:=True) &
> ",0))"
> .Value = .Value
> .Replace what:="True", replacement:="", _
> lookat:=xlWhole, MatchCase:=False
> .Replace what:="False", replacement:="No", _
> lookat:=xlWhole, MatchCase:=False
> .HorizontalAlignment = xlCenter
> End With
> Next iCol
>
> .Cells(1, MaxCols + 2).Value = "Count Of No's"
>
> With .Range(.Cells(2, MaxCols + 2), .Cells(LastRow, MaxCols +
> 2))
> .Formula = "=countif(B2:" & _
> .Parent.Cells(2, MaxCols + 1).Address(0, 0) &
> ",""no"")"
> .Value = .Value
> .HorizontalAlignment = xlCenter
> End With
>
> .Range("a1", .Cells(LastRow, MaxCols + 2)).AutoFilter
>
> Application.Goto .Range("a1"), Scroll:=True
> .Range("b2").Select
> ActiveWindow.FreezePanes = True
>
> .UsedRange.Columns.AutoFit
>
> Set DestCell = .UsedRange 'try to reset last used cell
>
> End With
>
> End Sub
>
> David wrote:
>>
>> Dave Peterson wrote
>>
>> > Since you have 4 different lists, how about an alternative?
>> >
>> > Create a new sheet (call it sheet2)
>> > Put Name in A1
>> > Copy the 4 lists into column A of this new sheet (one under the
>> > other)
>> >
>> > Then select that range (A1:A###)
>> > data|filter|advanced filter
>> > Copy to another location
>> > List range: (should be entered (a1:A###)
>> > copy to: B1
>> > Check Unique records only box
>> >
>> > Now you have a list of unique names in column B.
>> > Delete column A (we're done with it).
>> >
>> > In B1, put: On List 1
>> > In C1, put: On List 2
>> > in D1, put: On List 3
>> > in E1, put: On list 4
>> >
>> > In B2, put this formula:
>> > =isnumber(match(a2,sheet1!a:a,0))
>> >
>> > In C2, put this:
>> > =isnumber(match(a2,sheet1!b:b,0))
>> >
>> > In D2:
>> > =isnumber(match(a2,sheet1!c:c,0))
>> >
>> > In E2:
>> > =isnumber(match(a2,sheet1!D:D,0))
>> >
>> > And drag down as far as column A extends.
>> >
>> > Now select columns A:E and do Data|Filter|Autofilter.
>> >
>> > You can filter to show the Falses in any column to see where its
>> > missing.
>>
>> Well, things broke when I tried to copy the 2nd list (and presumably
>> would do the same with 3rd and 4th) with a #REF to cells from that
>> 2nd list, which references cells from a 2nd external file. I didn't
>> pursue things any further. Remember these lists are "built" as a
>> result of links to external files. If I pasted values, wouldn't I
>> have to repeat your proposed process each time one or more lists
>> change (quite often)? Basically this is a test file to insure I have
>> not missed adding/deleting a name from all 4 files. I can immediately
>> see if things don't match when length of lists differ, but then have
>> to scan visually to see what name starts the difference. Additionally
>> each source range is from a different range in each of the source
>> workbooks.
>>
>> Here's what I want:
>> If name is in A, but not in B or C or D, I want to know
>> If name is in B, but not in A or C or D, I want to know
>> If name is in C, but not in A or B or D, I want to know
>> If name is in D, but not in A or B or C, I want to know
>>
>> Am I asking for 4 conditions, thereby exceeding CF's limit of 3? If
>> so, I'll accept 3 out of 4 <g>.
>>
>> --
>> David
>

That's the way the code was written--using row 1 as headers and data starting in
row 2.

Did you have trouble when you tried it?

David wrote:
>
> Thanks very much for this code. One small detail I've tried to fiddle
> with and can't get around. I'm sure it's simple. My lists on Sheet1 each
> start in Row2, with Row1 being headers. How to adjust?
>
> --
> David
>
> Dave Peterson wrote
>
> > Personally, I find Format|Conditional formatting very pretty--but
> > pretty much useless. You can't (easily) count the missing items; you
> > can't filter by that conditional formatting color.
> >
> > If I had to do it over and over and over, I'd record a macro when I
> > did those steps (including the paste|special|Values). Then just rerun
> > that whenever I needed the info.
> >
> > This seemed to work ok for me:
> >
> > Option Explicit
> > Sub testme()
> > Dim CurWks As Worksheet
> > Dim NewWks As Worksheet
> > Dim iCol As Long
> > Dim DestCell As Range
> > Dim MaxCols As Long
> > Dim LastRow As Long
> >
> > Set CurWks = Worksheets("sheet1")
> >
> > Set NewWks = Worksheets.Add
> > Set DestCell = NewWks.Range("a2")
> >
> > With CurWks
> > MaxCols = .Cells(1, .Columns.Count).End(xlToLeft).Column
> > For iCol = 1 To MaxCols
> > .Range(.Cells(2, iCol), .Cells(.Rows.Count,
> > iCol).End(xlUp)).Copy DestCell.PasteSpecial
> > Paste:=xlPasteValues With NewWks
> > Set DestCell = .Cells(.Rows.Count,
> > "A").End(xlUp).Offset(1, 0)
> > End With
> > Next iCol
> > End With
> >
> > With NewWks
> > .Range("a1").Value = "Name"
> > .Range("A1", .Cells(.Rows.Count,
> > "A").End(xlUp)).AdvancedFilter _
> > Action:=xlFilterCopy, copytorange:=.Range("B1"),
> > unique:=True
> >
> > .Range("a1").EntireColumn.Delete
> >
> > .Range("a1").EntireColumn.Sort key1:=.Range("a1"), _
> > order1:=xlAscending,
> > header:=xlYes
> >
> > LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
> >
> > For iCol = 1 To MaxCols
> > .Cells(1, iCol + 1).Value = "On List#" & iCol
> > With .Range(.Cells(2, iCol + 1), .Cells(LastRow, iCol +
> > 1))
> > .Formula = "=isnumber(match(A2," _
> > & CurWks.Columns(iCol).Address(external:=True) &
> > ",0))"
> > .Value = .Value
> > .Replace what:="True", replacement:="", _
> > lookat:=xlWhole, MatchCase:=False
> > .Replace what:="False", replacement:="No", _
> > lookat:=xlWhole, MatchCase:=False
> > .HorizontalAlignment = xlCenter
> > End With
> > Next iCol
> >
> > .Cells(1, MaxCols + 2).Value = "Count Of No's"
> >
> > With .Range(.Cells(2, MaxCols + 2), .Cells(LastRow, MaxCols +
> > 2))
> > .Formula = "=countif(B2:" & _
> > .Parent.Cells(2, MaxCols + 1).Address(0, 0) &
> > ",""no"")"
> > .Value = .Value
> > .HorizontalAlignment = xlCenter
> > End With
> >
> > .Range("a1", .Cells(LastRow, MaxCols + 2)).AutoFilter
> >
> > Application.Goto .Range("a1"), Scroll:=True
> > .Range("b2").Select
> > ActiveWindow.FreezePanes = True
> >
> > .UsedRange.Columns.AutoFit
> >
> > Set DestCell = .UsedRange 'try to reset last used cell
> >
> > End With
> >
> > End Sub
> >
> > David wrote:
> >>
> >> Dave Peterson wrote
> >>
> >> > Since you have 4 different lists, how about an alternative?
> >> >
> >> > Create a new sheet (call it sheet2)
> >> > Put Name in A1
> >> > Copy the 4 lists into column A of this new sheet (one under the
> >> > other)
> >> >
> >> > Then select that range (A1:A###)
> >> > data|filter|advanced filter
> >> > Copy to another location
> >> > List range: (should be entered (a1:A###)
> >> > copy to: B1
> >> > Check Unique records only box
> >> >
> >> > Now you have a list of unique names in column B.
> >> > Delete column A (we're done with it).
> >> >
> >> > In B1, put: On List 1
> >> > In C1, put: On List 2
> >> > in D1, put: On List 3
> >> > in E1, put: On list 4
> >> >
> >> > In B2, put this formula:
> >> > =isnumber(match(a2,sheet1!a:a,0))
> >> >
> >> > In C2, put this:
> >> > =isnumber(match(a2,sheet1!b:b,0))
> >> >
> >> > In D2:
> >> > =isnumber(match(a2,sheet1!c:c,0))
> >> >
> >> > In E2:
> >> > =isnumber(match(a2,sheet1!D:D,0))
> >> >
> >> > And drag down as far as column A extends.
> >> >
> >> > Now select columns A:E and do Data|Filter|Autofilter.
> >> >
> >> > You can filter to show the Falses in any column to see where its
> >> > missing.
> >>
> >> Well, things broke when I tried to copy the 2nd list (and presumably
> >> would do the same with 3rd and 4th) with a #REF to cells from that
> >> 2nd list, which references cells from a 2nd external file. I didn't
> >> pursue things any further. Remember these lists are "built" as a
> >> result of links to external files. If I pasted values, wouldn't I
> >> have to repeat your proposed process each time one or more lists
> >> change (quite often)? Basically this is a test file to insure I have
> >> not missed adding/deleting a name from all 4 files. I can immediately
> >> see if things don't match when length of lists differ, but then have
> >> to scan visually to see what name starts the difference. Additionally
> >> each source range is from a different range in each of the source
> >> workbooks.
> >>
> >> Here's what I want:
> >> If name is in A, but not in B or C or D, I want to know
> >> If name is in B, but not in A or C or D, I want to know
> >> If name is in C, but not in A or B or D, I want to know
> >> If name is in D, but not in A or B or C, I want to know
> >>
> >> Am I asking for 4 conditions, thereby exceeding CF's limit of 3? If
> >> so, I'll accept 3 out of 4 <g>.
> >>
> >> --
> >> David
> >

--

Dave Peterson

Dave Peterson wrote

> That's the way the code was written--using row 1 as headers and data
> starting in row 2.
>
> Did you have trouble when you tried it?

That's what it looked like to me, looking at the various .Cell references.
For some reason, A2 & F2 on Sheet2 wind up with a 0 (zero) in them. Could
it be because I have an equal number of rows of "padding" at the bottom of
the 4 columns in Sheet1? They contain formulas that evaluate to zero from
each of the linked files in case I add people to those lists.

--
David

Dave Peterson wrote

> That's the way the code was written--using row 1 as headers and data
> starting in row 2.
>
> Did you have trouble when you tried it?

Ok, as an experiment, I deleted the rows that evaluated to zero and things
on Sheet2 wind up fine now. Would like to keep the padded range, though, so
I may have to live with the zero's.

--
David

If you use formulas that look like:
=sheet1!a1

You'll see that they return a 0 if A1 is empty.

I like this formula better:
=if(sheet1!a1="","",sheet1!a1)

Then my "padded" range looks empty. This won't change the results of the
macro--you'll see blanks instead of 0's, though.


David wrote:
>
> Dave Peterson wrote
>
> > That's the way the code was written--using row 1 as headers and data
> > starting in row 2.
> >
> > Did you have trouble when you tried it?
>
> Ok, as an experiment, I deleted the rows that evaluated to zero and things
> on Sheet2 wind up fine now. Would like to keep the padded range, though, so
> I may have to live with the zero's.
>
> --
> David

--

Dave Peterson

Dave Peterson wrote

> If you use formulas that look like:
> =sheet1!a1
>
> You'll see that they return a 0 if A1 is empty.
>
> I like this formula better:
> =if(sheet1!a1="","",sheet1!a1)
>
> Then my "padded" range looks empty. This won't change the results of the
> macro--you'll see blanks instead of 0's, though.

I opted for this "dirty" fix toward the end of the macro:
Application.Goto .Range("a1"), Scroll:=True
.Range("b2").Select
ActiveWindow.FreezePanes = True
.Rows(2).Delete '<---
.UsedRange.Columns.AutoFit

--
David

Or maybe to preserve integrity:

..Rows(2).Hidden = True

--
David

I think I'd check to see if that cell was empty first.

if .range("B2").value = "" then
.rows(2).delete
end if
Application.Goto .Range("a1"), Scroll:=True
.range("b2").select
ActiveWindow.FreezePanes = True
.UsedRange.Columns.AutoFit

Just in case that "padded row" was actually used.

David wrote:
>
> Dave Peterson wrote
>
> > If you use formulas that look like:
> > =sheet1!a1
> >
> > You'll see that they return a 0 if A1 is empty.
> >
> > I like this formula better:
> > =if(sheet1!a1="","",sheet1!a1)
> >
> > Then my "padded" range looks empty. This won't change the results of the
> > macro--you'll see blanks instead of 0's, though.
>
> I opted for this "dirty" fix toward the end of the macro:
> Application.Goto .Range("a1"), Scroll:=True
> .Range("b2").Select
> ActiveWindow.FreezePanes = True
> .Rows(2).Delete '<---
> .UsedRange.Columns.AutoFit
>
> --
> David

--

Dave Peterson

I'd just check, then (maybe) delete.

David wrote:
>
> Or maybe to preserve integrity:
>
> .Rows(2).Hidden = True
>
> --
> David

--

Dave Peterson

Good point. Thanks for all your help

--
David

Dave Peterson wrote

> I think I'd check to see if that cell was empty first.
>
> if .range("B2").value = "" then
> .rows(2).delete
> end if
> Application.Goto .Range("a1"), Scroll:=True
> .range("b2").select
> ActiveWindow.FreezePanes = True
> .UsedRange.Columns.AutoFit
>
> Just in case that "padded row" was actually used.

Dave Peterson wrote

> I'd just check, then (maybe) delete.
>
> David wrote:
>>
>> Or maybe to preserve integrity:
>>
>> .Rows(2).Hidden = True
>>
>> --
>> David
>