Help with VBA Code to select a range that include blank rows

[t.powley]

I hope someone can help,

I have this macro that finds all data in columns A:D, creates a chart from that data, saves the chart as a .png image then deletes the chart.

It works perfectly when the are no blank rows, but as soon as there is a blank row it will only create the chart from the first blank row up.

I need the chart to be created from the very last row of data up.

Sub printbulletin()

LstRwInD = Cells("D" & Rows.Count).End(xlUp).Row
Set Rng = Excel.Range("A1:D" & LstRwInD).CurrentRegion
      Rng.CopyPicture xlScreen, xlPicture
Set cht = ActiveSheet.ChartObjects.Add(0, 0, Rng.Width + 0.01, Rng.Height + 0.01)
cht.Chart.Paste
cht.Chart.Export strPath & "T:\Homepage\staffbulletin.png"
ActiveSheet.ChartObjects.Delete

End Sub

 

[MARK858]

Try removing the .CurrentRegion

 

[Rick Rothstein]

I have this macro that finds all data in columns A:D, creates a chart from that data, saves the chart as a .png image then deletes the chart.

It works perfectly when the are no blank rows, but as soon as there is a blank row it will only create the chart from the first blank row up.

I need the chart to be created from the very last row of data up.

Sub printbulletin()

LstRwInD = Cells("D" & Rows.Count).End(xlUp).Row
Set Rng = Excel.Range("A1:D" & LstRwInD).CurrentRegion
      Rng.CopyPicture xlScreen, xlPicture
Set cht = ActiveSheet.ChartObjects.Add(0, 0, Rng.Width + 0.01, Rng.Height + 0.01)
cht.Chart.Paste
cht.Chart.Export strPath & "T:\Homepage\staffbulletin.png"
ActiveSheet.ChartObjects.Delete

End Sub

Remove the .CurrentRegion (shown in red above)... you already have the full range with Range("A1:D" & LstRwInD).

 

[t.powley]

Remove the .CurrentRegion (shown in red above)... you already have the full range with Range("A1:D" & LstRwInD).

Thank you, now works as required.

I'm still learning by hacking together VBA scripts so I'm slowly learning from mistakes and advice, having these comments is very helpfull.

 

[t.powley]

Try removing the .CurrentRegion

Thank you, Mark