CODEWITHSUNDEEP
excelvba
ianmko
ianmko

Reputation: 25

If a row is empty, how do I copy/paste data from the row below it to populate the empty row?

I have a spreadsheet with numerous blank rows, and I need to extract specific data from cells below each blank row to populate the blank one. I'm just not good enough yet at VBA to figure out how to do this complex set of steps, so thank you in advance, and sorry to the developers who don't like how to do this questions.

I've broken down the steps I need.

  1. Determine if row is empty.
  2. If true, copy text from column A of the row below the empty row.
  3. Paste text into column A of the empty row.
  4. Change last three digits of text to "XXX".
  5. Continue until end of spreadsheet.

EDIT: Cells as they currently are:

Current

What I want them to look like:

Future

I'm trying to do this for every set, but there may be 2, 3, 4, or more groups of rows with a blank row above them. So the VBA needs to include all of the rows in that group.

Upvotes: 0

Views: 565

Answers (1)

Ric Johnston
Ric Johnston

Reputation: 16

This sounds like what you want. If your numbers are numeric you'll want to convert them to text before your try to take out digits.

Sub dostuff()

For ir = 1 To 10000

   If (Cells(ir, 1).value = "" And Cells(ir + 1, 1).value = "") Then _
      Exit For 

   If (Cells(ir, 1).value = "") Then _
      Cells(ir, 1).value = _ 
          Mid(Format(Cells(ir + 1, 1).value,"00000000"),1,5) & "XXX"

 Next ir


 End Sub

Upvotes: 0

Related Questions