How to Relocate a row of cells automatically based on the value in the cell

MO24 · Feb 3rd 2022

I need a code that will find a specified value/word in the 'B' column and then move a selection of cells (A6:E6) to (G3:K3). Can anyone help me?

vcoolio · Feb 7th 2022

Hello Mo,

You had best upload a sample of your workbook and also give us a precise description of what you intend to do.

Cheerio,

vcoolio.

rollis13 · Feb 7th 2022

From Marius44 :

Code

Option Explicit

Sub Trial_ONE()
Dim i As Long, a As Long
Dim rg, rg2, rg3
Range("D:D").ClearContents
For i = 1 To 2
  On Error Resume Next
  rg = Split(Cells(i, 1), ":")
  If Len(rg(1)) = 8 Then
    rg2 = Split(Cells(i, 1), "-")
    rg3 = Split(rg2(2), ":")
  End If
  On Error GoTo 0
  a = a + 1
  If Len(rg(1)) > 8 Then
    Cells(a, 4) = rg(0)
    a = a + 1
    Cells(a, 4) = rg(1)
  ElseIf Len(rg(1)) = 8 Then
    Cells(a, 4) = rg2(0) & "-" & rg3(0)
    a = a + 1
    Cells(a, 4) = Right(rg2(1), 4) & "-" & rg3(0)
  End If
Next
End Sub

Display More

The macro writes data in column D (so you can compare them to the desired column).

MO24 · Feb 7th 2022

These are the main points I am trying to achieve

- I will enter into the cells Text Values from drop down boxes

- It then recognises if the text entered is equal to "Assessment" in the "B" column/"Level" Column in "Table1" . (If it is another text value then it leaves it. EG. Class)

- It then moves the whole row (Not the worksheet row eg, "E" Just the table row) that the "Assessment" value is found in to "Table2" while retaining the data that is existing there. I also need it to retain the existing formatting so once the row has been copied over I am still able to use the dropdown box to enter another row.

- It then runs the existing macro that sorts the dates from oldest to newest

Does that help?

Thanks,

MO

How to Relocate a row of cells automatically based on the value in the cell

Participate now!

Share

Tags