VBA: Moving Cell content to specific column if empty cell within range

ChetManley

New Member
Joined
Dec 9, 2019
Messages
33
Office Version
  1. 2010
Platform
  1. Windows
Good day everyone,

I am trying to find a solution for a predicament I have, let me provide an example of the table I'm working with:

A​
B​
C​
D​
E​
F​
G​
H​
I​
J​
1​
Customer NumbSerialCustomer NameAddress_Line1Address_Line2Address_Line3Address_Line4Address_Line5Email AddressTelephone
2​
1223X12UINMR JOHN SMITHFLAT 1YELLOW RDPO BOX 123LONDONUNITED KINGDOMsmith@test.com445566879
3​
44321LLP133MS ELLEN RIPLEYLV-426 ACHERONUNIT 321NEW YORKUNITED STATESripley@wyc.com16598212
4​
55488999XX921OMR NICK FURY33PIAZZA SAN MARCOVENICEITALYfury@avengers.com

This is an example of a data extract that I would be able to pull from my database, what I would like to do is, if there is a blank cell in the range G:H I want the data to move right so that columns G and H always have data - I can leave col F as blank if necessary, as an example, looking at row 3:

A​
B​
C​
D​
E​
F​
G​
H​
I​
J​
1​
Customer NumbSerialCustomer NameAddress_Line1Address_Line2Address_Line3Address_Line4Address_Line5Email AddressTelephone
3​
44321LLP133MS ELLEN RIPLEYLV-426 ACHERONUNIT 321NEW YORKUNITED STATESripley@wyc.com16598212

As you can see the data from F:G moved Right to the range G:H;

My ask is if there is a way to automate this work as I will be dealing with tens of thousands of cases as these.

My thanks in advance,
CM
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
Could you have a situation where both G & H are blank? If so what should happen?
 
Upvote 0
Could you have a situation where both G & H are blank? If so what should happen?
Hi Fluff,

It is highly unlikely but if it happens I would pick those up separately prior to this exercise because they would follow a different process

Cheers
CM
 
Upvote 0
Ok, how about
VBA Code:
Sub ChetManley()
   Dim Rng As Range
   On Error Resume Next
   For Each Rng In Range("H2:H" & Range("A" & Rows.Count).End(xlUp)).SpecialCells(xlBlanks).Areas
      Rng.Value = Rng.Offset(, -1).Value
      Rng.Offset(, -1).Value = ""
   Next Rng
   For Each Rng In Range("G2:G" & Range("A" & Rows.Count).End(xlUp)).SpecialCells(xlBlanks).Areas
      Rng.Value = Rng.Offset(, -1).Value
      Rng.Offset(, -1).Value = ""
   Next Rng
   On Error GoTo 0
End Sub
 
Upvote 0
Solution
Ok, how about
VBA Code:
Sub ChetManley()
   Dim Rng As Range
   On Error Resume Next
   For Each Rng In Range("H2:H" & Range("A" & Rows.Count).End(xlUp)).SpecialCells(xlBlanks).Areas
      Rng.Value = Rng.Offset(, -1).Value
      Rng.Offset(, -1).Value = ""
   Next Rng
   For Each Rng In Range("G2:G" & Range("A" & Rows.Count).End(xlUp)).SpecialCells(xlBlanks).Areas
      Rng.Value = Rng.Offset(, -1).Value
      Rng.Offset(, -1).Value = ""
   Next Rng
   On Error GoTo 0
End Sub
Hi Fluff, I will try this tomorrow at work and will let you know!

Many thanks
CM
 
Upvote 0
You're welcome & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,208
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top