Excel Union Macro returns inorrect values

March 16, 2011 at 00:35:27
Specs: Windows XP
Hi guys,

I have been reading your site for a while and I could find almost all the answers to my questions. I'm very good at Excel, but my visual basics knowledge is very limited.
I want to write a macro which selects and copies 4 different ranges. These ranges have various length and starting points, they may include several rows, just one or none. Each range has a title that I could look up with a "Find" macro. I have put together the code below. It works well until I get to the last step where I want to select all 4 ranges at once. I tried Union (a,b,c,d), but it's selecting the incorrect rows and cells.

I don't know what I'm doing wrong and would REALLY appreciate if one of you could help me, because I have been staring at this problem way too long...

Many thanks!!!
Kata

Sub Upload_Data()

Application.ScreenUpdating = False

Dim Choose_Country, Choose_Product As Variant
Dim rng, rng2, rng3, rng4, rng5 As Range
Dim r As Range, r1 As Range, r2 As Range
Dim a, b, c, d As Range

Sheets("Summary").Select

Set rng2 = Range("A:A").Find(What:="Primary Suppliers", After:=[A1], LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False). _
Offset(1, 0)
If rng2 = "" Then
Set a = Nothing
Else
If rng2 <> 0 And rng2.Offset(1, 0) = 0 Then
Set a = rng2.EntireRow
Else
Set a = Range(rng2, rng2.End(xlDown)).EntireRow
End If
End If
a.Select


Set rng3 = Columns(1).Find(What:="Secondary Suppliers", After:=[A1], LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False). _
Offset(1, 0)
If rng3 = "" Then
Set b = Nothing
Else
If rng3 <> "" And rng3.Offset(1, 0).Value = "" Then
Set b = rng3.EntireRow
Else
Set b = Range(rng3, rng3.End(xlDown)).EntireRow
End If
End If
b.Select


Set rng4 = Columns(1).Find(What:="Import", After:=[A1], LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False). _
Offset(1, 0)
If rng4 = "" Then
Set c = Nothing
Else
If rng4 <> "" And rng4.Offset(1, 0).Value = "" Then 'And rng3.Offset(1, 0).Value = ""
Set c = rng4.EntireRow
Else
Set c = Range(rng4, rng4.End(xlDown)).EntireRow
End If
End If
c.Select

'Set c = Range(rng4, rng4.End(xlDown)).EntireRow
'c.Select

Set rng5 = Range("A:A").Find(What:="Export", After:=[A23], LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False). _
Offset(1, 0)
If rng5 = "" Then
Set d = Nothing
Else
If rng5 <> "" And rng5.Offset(1, 0).Value = "" Then
Set d = rng5.EntireRow
Else
Set d = Range(rng5, rng5.End(xlDown)).EntireRow
End If
End If
d.Select

Union(a,b,c,d).Select
'...


See More: Excel Union Macro returns inorrect values

Report •

#1
March 16, 2011 at 04:53:54
Please request the moderator to move this post to Office Forum. You will get people immediately who do this more regularly.

You have to be a little bit crazy to keep you from going insane.


Report •
Related Solutions


Ask Question