VBA help in filling blank cells in a range

[el_lloydie]

Hi guys,

Hoping for a bit of help please.

I have the following VBA code

Private Sub Spendreports()
Dim ws As Worksheet
Set ws = Sheets.Add(Before:=Worksheets(6))
ws.Name = "Data"

Dim s1 As Worksheet
Dim s2 As Worksheet
Dim s3 As Worksheet
Set s1 = Sheets("Data")
Set s2 = Sheets("Reactive Data")
Set s3 = Sheets("Planned Data")

s1.Range("A1").Value = "Client Name"
s1.Range("B1").Value = "Branch No"
s1.Range("C1").Value = "Occupant Name"
s1.Range("D1").Value = "Supplier"
s1.Range("E1").Value = "Requested At"
s1.Range("F1").Value = "Completion Due At"
s1.Range("G1").Value = "Origin"
s1.Range("H1").Value = "Job Code"
s1.Range("I1").Value = "Task Number"
s1.Range("J1").Value = "Reporting Primary Category"
s1.Range("K1").Value = "Reporting Secondary Category"
s1.Range("L1").Value = "Asset"
s1.Range("M1").Value = "Sub Asset"
s1.Range("N1").Value = "Description"
s1.Range("O1").Value = "Priority"
s1.Range("P1").Value = "Invoiced Cost"
s1.Range("Q1").Value = "Cert Required"
s1.Range("R1").Value = "Finance Type"
s1.Range("S1").Value = "Status"
s1.Range("T1").Value = "Postcode"
s1.Range("U1").Value = "Postcode Prefix"
s1.Range("V1").Value = "Lens Area"

s1.Range("A1:V1").HorizontalAlignment = xlCenter
s1.Range("A1:V1").VerticalAlignment = xlCenter
s1.Range("A1:V1").Font.Bold = True
s1.Range("A1:V1").Font.Name = "Calibri"
s1.Range("A1:V1").Font.Size = 11

Dim c As Range
Dim SrchRng

Set SrchRng = s2.Range("G2", s2.Range("G1048576").End(xlUp))
Do
Set c = SrchRng.Find("HP", LookIn:=xlValues)
If Not c Is Nothing Then c.EntireRow.Delete
Loop While Not c Is Nothing

Set SrchRng = s2.Range("G2", s2.Range("G1048576").End(xlUp))
Do
Set c = SrchRng.Find("GP", LookIn:=xlValues)
If Not c Is Nothing Then c.EntireRow.Delete
Loop While Not c Is Nothing

s2.Range("A2:R1048576").Sort Key1:=s2.Range("G2:G1048576"), Order1:=xlAscending, Header:=xlYes

s2.Range("A2:E1048567").Copy s1.Range("A2")
s2.Range("F2:G1048567").Copy s1.Range("G2")
s2.Range("H2:K1048567").Copy s1.Range("J2")
s2.Range("L2:M1048567").Copy s1.Range("O2")
s2.Range("N2:R1048567").Copy s1.Range("R2")

s1.Range(Range("A2"), Range("V2").End(xlDown)).Select

End Sub

Can anyone help me input the string "#N/A" in the blank cells of the range I've selected at the end of my code please? It's got me flummoxed!

Many thanks for your help
Gareth

 

[Rick Rothstein]

Try changing this line of code...

s1.Range(Range("A2"), Range("V2").End(xlDown)).Select

to this...

On Error Resume Next
s1.Range(Range("A2"), Range("V2").End(xlDown)).SpecialCells(xlBlanks).Value = "#N/A"
On Error GoTo 0

The two On Error statements are there to protect against there being no blank cells in the range... if you are absolutely sure there will always be at least on blank cell in the range, then they can be omitted.

 

[el_lloydie]

Thanks for this, works well - except it stops working from row 1367. This row onwards still has blanks. I've tried playing around with it to no avail.

Any thoughts?

Your help is much appreciated.

Try changing this line of code...

s1.Range(Range("A2"), Range("V2").End(xlDown)).Select

to this...

On Error Resume Next
s1.Range(Range("A2"), Range("V2").End(xlDown)).SpecialCells(xlBlanks).Value = "#N/A"
On Error GoTo 0

The two On Error statements are there to protect against there being no blank cells in the range... if you are absolutely sure there will always be at least on blank cell in the range, then they can be omitted.

 

[Rick Rothstein]

Thanks for this, works well - except it stops working from row 1367. This row onwards still has blanks.

You have data scattered in and among the cells after Row 1367?

If yes, what is the last row that contains any data within your column range?

Also, what version of Excel are you using?

 

[el_lloydie]

Hi

Yes I have data scattered in and among the cells after Row 1367.

The last row that actually contains any data is 85864.

I am using Excel 2010 Standard.

Thanks
Gareth

You have data scattered in and among the cells after Row 1367?

If yes, what is the last row that contains any data within your column range?

Also, what version of Excel are you using?