Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code Tweak
Columns("A").SpecialCells(xlBlanks).Offset(-1).Select
This code does what I want it to, It will select all blank spaces, then select one cell above it. Now, i want to take it a step further and have it select every row from that one up to the next blank above it. And if the selection does not equal 30 spaces, I want it to add blank rows to it to become 30 rows. SO for example The selection is 14 rows, I want it to add 16 empty rows. Make sense??? Range(ActiveCell, ActiveCell(30 - _ActiveCell.Value, 1)).EntireRow.Insert Wil this work for part of it? So what will the complete code look like? Thanks!!!!!! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code Tweak
This seems like a pretty weird question. I'm gonna guess that you want to make
sure that there's 30 empty rows between each group--and your question was just a simplified version of that (and that you'd modify the code to do the looping). If I'm right, keep reading. I'm gonna assume that all my cells in column A are constants or empty--no formulas! If that's the case, this worked ok for me: Option Explicit Sub testme() Dim myAreas As Range Dim aCtr As Long Dim myRng As Range Dim myConstRng As Range Dim TopRowOfBottomArea As Long Dim BotRowOfTopArea As Long Dim HowManyRowsBetween As Long Dim wks As Worksheet Set wks = Worksheets("sheet1") With wks Set myRng = .Range("a:a") Set myConstRng = Nothing On Error Resume Next Set myConstRng = myRng.Cells.SpecialCells(xlCellTypeConstants) On Error GoTo 0 If myConstRng Is Nothing Then MsgBox "no constants!" Exit Sub End If For aCtr = myConstRng.Areas.Count To 2 Step -1 TopRowOfBottomArea = myConstRng.Areas(aCtr).Row With myConstRng.Areas(aCtr - 1) 'area just on top BotRowOfTopArea = .Cells(.Cells.Count).Row End With HowManyRowsBetween = TopRowOfBottomArea - BotRowOfTopArea - 1 If HowManyRowsBetween = 30 Then 'do nothing--not even delete extra rows??? Else .Rows(TopRowOfBottomArea - 1) _ .Resize(30 - HowManyRowsBetween).Insert End If Next aCtr End With End Sub This essentially selects column A and does edit|goto|special cells|constants Then it starts at the bottom and looks at each of the little areas within column A. It looks at the top row of the bottom area and the bottom row of the area just on top of it. And cycles from the bottom up. bodhisatvaofboogie wrote: Columns("A").SpecialCells(xlBlanks).Offset(-1).Select This code does what I want it to, It will select all blank spaces, then select one cell above it. Now, i want to take it a step further and have it select every row from that one up to the next blank above it. And if the selection does not equal 30 spaces, I want it to add blank rows to it to become 30 rows. SO for example The selection is 14 rows, I want it to add 16 empty rows. Make sense??? Range(ActiveCell, ActiveCell(30 - _ActiveCell.Value, 1)).EntireRow.Insert Wil this work for part of it? So what will the complete code look like? Thanks!!!!!! -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need someone to help tweak a code | Excel Discussion (Misc queries) | |||
Newby Needs minor tweak on this VBA Macro code for Excel | Excel Programming | |||
Can someone please tweak my Macro? | Excel Discussion (Misc queries) | |||
excel code tweak for outlook - confusing | Excel Programming | |||
Need final code tweak | Excel Programming |