ERROR 400 on identical codes different range

[L. Howard]

Code 1 works fine on A, B, C, D.

With identical data in H, I, J, K code 2 errors.

What the @#$% am I overlooking?

Thanks,
Howard

Option Explicit

Sub MyDupesGone1()

With Sheets("Sheet4").Range("A1", Range("D1").End(xlDown))
.RemoveDuplicates Columns:=Array(1, 2, 3, 4), _
Header:=xlNo
End With
End Sub


Sub MyDupesGone2()

With Sheets("Sheet4").Range("H1", Range("K1").End(xlDown))
.RemoveDuplicates Columns:=Array(8, 9, 10, 11), _
Header:=xlNo
End With
End Sub

 

[Claus Busch]

Hi Howard,

Am Wed, 26 Nov 2014 23:30:04 -0800 (PST) schrieb L. Howard:

With identical data in H, I, J, K code 2 errors.

if your range is H":K & lrow
you also have to write Array (1, 2, 3, 4)
The columns in the array refer to the first column in the range and that
is column H

Sub MyDupesGone2()
With Sheets("Sheet4").Range("H1", Range("K1").End(xlDown))
.RemoveDuplicates Columns:=Array(1, 2, 3, 4), _
Header:=xlNo
End With
End Sub

Regards
Claus B.

 

[L. Howard]

Hi Howard,

Am Wed, 26 Nov 2014 23:30:04 -0800 (PST) schrieb L. Howard:


if your range is H":K & lrow
you also have to write Array (1, 2, 3, 4)
The columns in the array refer to the first column in the range and that
is column H

Sub MyDupesGone2()
With Sheets("Sheet4").Range("H1", Range("K1").End(xlDown))
.RemoveDuplicates Columns:=Array(1, 2, 3, 4), _
Header:=xlNo
End With
End Sub

Regards
Claus B.

OBoy! They are the elements NOT the column Headers/numbers!


Thanks for your patience.

Howard

 

[dguillett]

Code 1 works fine on A, B, C, D.

With identical data in H, I, J, K code 2 errors.

What the @#$% am I overlooking?

Thanks,
Howard

Option Explicit

Sub MyDupesGone1()

With Sheets("Sheet4").Range("A1", Range("D1").End(xlDown))
.RemoveDuplicates Columns:=Array(1, 2, 3, 4), _
Header:=xlNo
End With
End Sub


Sub MyDupesGone2()

With Sheets("Sheet4").Range("H1", Range("K1").End(xlDown))
.RemoveDuplicates Columns:=Array(8, 9, 10, 11), _
Header:=xlNo
End With
End Sub

Did not test but wonder if this would not work using a1 for last row

With Sheets("Sheet4").Range("A1", Range("a1").End(xlDown))
.RemoveDuplicates Columns:=Array(1,2,3,4,8,9,10,11, _
Header:=xlNo
End With

 

[GS]

Did not test but wonder if this would not work using a1 for last row

This has been thoroughly tested and works a peach for me to find the
last row/col that contains data...

Public Function GetLastDataPos&(Optional Wks As Worksheet, _
Optional IsRow As Boolean = True, Optional StartPos& = 1)
' Finds the last row or col of UsedRange that contains data.
' Allows for excluding any number of header rows/cols.
'
' Args:
' Wks Optional:
' Object ref to the sheet being searched.
' Defaults to ActiveSheet if missing.
'
' IsRow Optional:
' Boolean value that determines which axis to test.
'
' StartPos Optional:
' A type Long that specifies the start row/col of the
search.
' If omitted the search starts at A1.
'

Dim n&, k&, lLast&

If Wks Is Nothing Then Set Wks = ActiveSheet

With Wks.UsedRange
lLast = IIf(IsRow, .Rows.Count, .Columns.Count)
For n = lLast To StartPos Step -1
k = Application.CountA(IIf(IsRow, Wks.Rows(n), Wks.Columns(n)))
If k > 0 Then GetLastDataPos = n: Exit Function
Next 'n
End With 'Wks.UsedRange
End Function

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[L. Howard]

Hi Garry,

Can you give me a simple example of how to use the function?

I start by copying to a standard module... then what do I do on the sheet?

Howard

 

[GS]

Hi Garry,

Can you give me a simple example of how to use the function?

I start by copying to a standard module... then what do I do on the
sheet?

Howard

It's this simple...

Dim lLastRow&, lLastCol&, lNextRow&, lNextCol&

lLastRow = GetLastDataPos() 'to return row#
lLastCol = GetLastDataPos(IsRow:=False) 'to return col#

OR

LNextRow = GetLastDataPos() + 1
LNextCol = GetLastDataPos(IsRow:=False) + 1

...where any extra blank rows residual in UsedRange doesn't matter
because the function loops backwards until it finds a non-empty range.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[L. Howard]

It's this simple...

Dim lLastRow&, lLastCol&, lNextRow&, lNextCol&

lLastRow = GetLastDataPos() 'to return row#
lLastCol = GetLastDataPos(IsRow:=False) 'to return col#

OR

LNextRow = GetLastDataPos() + 1
LNextCol = GetLastDataPos(IsRow:=False) + 1

..where any extra blank rows residual in UsedRange doesn't matter
because the function loops backwards until it finds a non-empty range.

Okay, thanks.

Off to play with it.

Howard

 

[GS]

But have some fun, eh!!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion