Inserting Rows???

H

hce

Dear All

Is it possible to write vb code to insert a row below say D12 i
there's data in D12? and again if there's data in D13, insert a row an
so on... so that I will have an empty row in between all the rows wit
data... Would really appreciate any suggestion/feedback...

Cheers
kelvi
 
K

Kevin H. Stecyk

hce wrote..
Dear All

Is it possible to write vb code to insert a row below say D12 if
there's data in D12? and again if there's data in D13, insert a row and
so on... so that I will have an empty row in between all the rows with
data... Would really appreciate any suggestion/feedback...

Cheers
kelvin
Click to expand...

Kelvin,

The following VBA code will do the following:

-Find the last row of data
-Add a blank row after a row with data
-Will work backwards from the last row to row 12. (You can change Row 12 to
whatever you want).

To use the VBA code, Alt F11, Insert Module, paste code.

Then back to the normal spreadsheet, Alt F8, and Select AddRows.



Sub AddRows()

Dim r As Long
Dim LastRow As Long

Application.ScreenUpdating = False
LastRow = ActiveSheet.UsedRange.Row - 1 + _
ActiveSheet.UsedRange.Rows.Count
Application.ScreenUpdating = False
'\***You can change Row 12 below***
For r = LastRow To 12 Step -1
If Application.CountA(Rows(r)) <> 0 Then Rows(r + 1).Insert
Shift:=xlDown
Next r
End Sub


Good luck.

Regards,
Kevin
 
I

icestationzbra

hi kevin,

in this formula:

LastRow = ActiveSheet.UsedRange.Row - 1 + _
ActiveSheet.UsedRange.Rows.Count

why are you using "ActiveSheet.UsedRange.Row - 1"? i could not find out
the purpose.

thanks,

mac.
 
K

Kevin H. Stecyk

icestationzbra ...
hi kevin,

in this formula:

LastRow = ActiveSheet.UsedRange.Row - 1 + _
ActiveSheet.UsedRange.Rows.Count

why are you using "ActiveSheet.UsedRange.Row - 1"? i could not find out
the purpose.
Click to expand...

Hi Mac,

By definition, the row AFTER the used range is blank. (If it wasn't blank,
then the used range would be larger.) So there is no need to insert a row
after the last row of the used range.

Thus, I start the process one row prior to the last row of the used range.

That's it.

Does that help?

Regards,
Kevin
 
K

Kevin H. Stecyk

icestationzbra wrote...
hi kevin,

in this formula:

LastRow = ActiveSheet.UsedRange.Row - 1 + _
ActiveSheet.UsedRange.Rows.Count

why are you using "ActiveSheet.UsedRange.Row - 1"? i could not find out
the purpose.
Click to expand...

Hi,

**You should see my prior answer. It addresses your specific question.**

But also, you should look at the following link:
http://j-walk.com/ss/excel/tips/tip56.htm

It is John Walkenbach's rountine that deletes all empty rows. I patterned
my solution using John's routine. I just made some slight adaptatations to
it.

Hope that helps.

Regards,
Kevin
 
Top