Thread: how to copy paste "patients id #" to where it is blank

Excel 2003. Every month we do this. Therefore we think a macro would be ideal?? We export data to excel.)
We have three columns. (Patient ID Number, Patient Name, Medication). Sometimes Patient ID Numbers are blank although some are filled in for the same Patient Name. For example column B "Patient Name"----cells b6:b12 has the Patient Name, yet column A "Patient ID Number" cells a10:a12 are blank. How can we get the blanks filled in for the repective patient? And have this repeat for all the patients in the spreadsheet who are missing their Patient ID Numbers? I tried to attach a sample data spreadsheet of my issue but manage attachments would not open (white screen forever). Thanks in advance for your time and assistance!

Hi kwrightcltc

Welcome to the Forum!

You'll need code something like this

Sub FillColBlanks_Offset()
'by Rick Rothstein  2009-10-24
'fill blank cells in column with value above
'http://www.contextures.com/xlDataEntry02.html

' Select Column First 
   Dim Area As Range, LastRow As Long
  On Error Resume Next
  LastRow = Cells.Find(What:="*", SearchOrder:=xlRows, _
               SearchDirection:=xlPrevious, _
               LookIn:=xlFormulas).Row
  For Each Area In ActiveCell.EntireColumn(1).Resize(LastRow). _
               SpecialCells(xlCellTypeBlanks).Areas
    Area.Value = Area(1).Offset(-1).Value
  Next
End Sub

This code will fill the SELECTED Column with the value from above. If you need help adapting please post a sample of your file.

Click the button in the attchment and tell me if it is what you are after?