Enter Remaining Values From RangeB into Range A

[pwill]

Hi can anyone help with vba code?​

I have two ranges A and B that mirror each other with headers A-G

range A "T5:Z7" sheet1
range B "AC5:AI7" sheet1

range B calculates values from another sheet and range A keeps a record of remaining values​

​

when I have remaining values anywhere in 'range B' I want record them at the same position in 'range A' until all cells in range A are filled with values from range B by adding a line of code to commandbutton1

ie

Before

T

U

V

W

X

Y

Z

AA

AB

AC

AD

AE

AF

AG

AH

AI

AJ

AK

1

2

3

RngA recorded records

RngB Remaining values

4

A

B

C

D

E

F

G

A

B

C

D

E

F

G

5

188

6

22

499

200

7

10

55

177

188

8

After

T

U

V

W

X

Y

Z

AA

AB

AC

AD

AE

AF

AG

AH

AI

AJ

AK

1

2

3

RngA

RngB

4

A

B

C

D

E

F

G

A

B

C

D

E

F

G

5

188

6

22

200

499

7

10

55

177

188

8

<tbody>
</tbody>

Any help would be appreciated

Regards

pwill

 

[KolGuyXcel]

Try:

Sub macro1()
    Dim rng As Range, lr As Long
    lr = 0
    For Each cell In Union(Range("T1:Z1"), Range("AC1:AI1"))
        cell.Activate
        If lr < Cells(Rows.Count, ActiveCell.Column).End(xlUp).Row Then
            lr = Cells(Rows.Count, ActiveCell.Column).End(xlUp).Row
        End If
    Next
    Set rng = Range("AC1:AI" & lr)
    For Each cell In rng
        If cell.Value2 <> "" Then
            cell.Activate
            If ActiveCell.Offset(, -5).Value2 <> "" Then
                   ActiveCell.Offset(, -5).Value2 = ActiveCell.Value2
                   ActiveCell.ClearContents
           End If
        End If
    Next
End Sub

 

[Rick Rothstein]

Assuming your values are constants (that is, there are not formula in "RngB Remaining values"), and assuming the location they are to be move to is always going to be empty in order to receive them, then I think this "simple" loop should work...

[table="width: 500"]
[tr]
	[td]Sub MoveRemainingValues()
  Dim Ar As Range
  For Each Ar In Range("AC5:AI" & Columns("AC:AI").Find("*", , xlFormulas, , xlRows, xlPrevious, , , False).Row).SpecialCells(xlConstants).Areas
    Ar.Cut Ar.Offset(, -9)
  Next
End Sub[/td]
[/tr]
[/table]

 

[pwill]

Thanks for your reply's,

KolGuyXcel didn't work? but Rick Rothstien works fine, much appreciated

pwill
​