decreasing height of selected range

[idahodave]

Hello. I have a column of data with values in A5, A15, A25, A35, etc. The cells in between these are empty. I need a macro that will find the next cell in the column, select it and the empty cells below so I can fill down without filling over the next cell that contains a value. Basically, I'm trying to decrease the size of a selection (for the fill-down) by one cell in height. The specific cells and the spread between them will change periodically so, besides A5, I can't have this tied to specific cells. Here's my attempted code:


Range("A5").Select

Range(Selection, Selection.End(xlDown)).Select

Selection.FillDown

Selection.End(xlDown).Select


End Sub


Hopefully I'm clear with my explanation. I appreciate any help. Thanks

 

[SirJB7]

Hi, idahodave!


If let's say cell A1 is selected, what did you tried to mean with "find the next ... select it and the empty cells below"? You're referring to range A6:A14 or to A6:A65536 for 2003 or earlier Excel versions (or to A6:A1048576 if 2007 or greater) assuming that in column A there aren't any other non-empty cells than those of rows 5, 15, 25, 35...?


Regards!

 

[kchiba]

Hi Idahodave


Try this


Sub SelctResize()


Range("A5").Select

Range(Selection, Selection.End(xlDown)).Select

Selection.Resize(Selection.Rows.Count - 1).Select


Selection.FillDown

Selection.End(xlDown).Select


End Sub

 

[prasaddn]

Hi,


Like some manual method?


step 1. filter the data to show blanks, this will obvious show A6 which is blank, and needs to have data that is in A5 (which is now hidden).


step 2. use formula =A5

step 3. copy A6 and paste in all the blank cells that is visible to you.

step 4. unfilter

step 5. Copy entire column A:A and pastespecial values and formatting.


Done.


Regards,

Prasad DN

 

[prasaddn]

yes, you can code the same in VBA as well, using


for each c in selection.cells

if c.value = "" then c.offset(-1,0).value

loop

 

[idahodave]

Kchiba, your solution works perfectly for what I need.

I appreciate everyone's help.


Thanks!