Excel/VBA

Advertisement


Question
Hi Adelaide,

I am trying to write some VBA code which checks if a cell is empty, if it is not it moves to the cell below and tests that cell. If the cell is empty it copies the cell immediately above into the empty cell and moves down to the next cell and tests again until the end of the data range is reached END)

I belive that a do until loop could work for the example below which would start at the first cell (Red) and work down the list copying and pasting data into empty cells. I would like to use a command button to do this.

Data with blank cells

Red
Red
Red
Red

Blue
Blue

Yellow
Yellow
Yellow
Yellow
Yellow
Yellow
Yellow
Yellow

Green
Green
Green
Green
Green


END


Data after running the macro

Red
Red
Red
Red
Red
Blue
Blue
Blue
Yellow
Yellow
Yellow
Yellow
Yellow
Yellow
Yellow
Yellow
Yellow
Green
Green
Green
Green
Green
Green
Green
END

Hope this makes sense

Many Thanks

Answer
Hi Malcolm
How are you?

Please try the following:

Sub CopyAbove()
Dim R As Range
Set R = ActiveSheet.Range("A:A")
I = 2
Do While R.Cells(I, 1) <> "END"
  If IsEmpty(R.Cells(I, 1)) = True Then
     R.Cells(I, 1) = R.Cells(I - 1, 1)
  End If
  I = I + 1
Loop
End Sub


Cheers
Adelaide

About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Adelaide carvalho

Expertise

I will be able to answer any question on Excel and Visual Basic for Applications - class modules, recursive procedures and functions are my favourite

Experience


Past/Present Clients
Several readers of my Books think they are useful.

©2016 About.com. All rights reserved.