|
Add/Delete rows based on count
Hi all. I have blocks of data where column A is arranged as follows (note th 'name' will all be different, The text 'Starts:' is constant): Name1 Starts: rows of data (number of rows varies from 0 to 100 or more) Name2 Starts: rows of data Name3 Starts: rows of data Name4 Starts: rows of data Name5 Starts: rows of data and so on, for up to 20-30 name blocks. What I'm trying to do is to get the number of rows between a cel containg 'Starts:' and the next name in column A to = 22, including empty rows at the bottom. So if the number of 'data' rows is less than 20 (some blocks will hav 0 rows of data), I need to pad it out with empty rows, but if it's mor than 20 I need to delete the excess rows (starting at the bottom of th block). I then need to add an additional 2 empty rows so I end up wit the following: A1: Name1 A2: Starts: A3 to A22: data/empty rows A23: empty row A24: empty row A25: Name2 A26: Starts: A27 to A46: data/empty rows A47: empty row A48: empty row A49: Name3 A50: Starts: A51 to A70: data/empty rows A71: empty row A72: empty row All the way down the sheet. I have got as far as figuring out how to add the 2 empty rows I nee but have no idea where to head on the rest. Note that while I have onl used column A in the example, entire rows need to be considered as othe columns also contain data -- Mark ----------------------------------------------------------------------- Mark K's Profile: http://www.excelforum.com/member.php...fo&userid=1411 View this thread: http://www.excelforum.com/showthread.php?threadid=57324 |
Add/Delete rows based on count
Yous say that "entire rows need to be considered as other columns also
contain data." Does this mean you want to delete the entire row (if it needs to be deleted)? Or are you saying to be careful not to delete the other columns because they might have data in them? Mark K wrote: Hi all. I have blocks of data where column A is arranged as follows (note the 'name' will all be different, The text 'Starts:' is constant): Name1 Starts: rows of data (number of rows varies from 0 to 100 or more) Name2 Starts: rows of data Name3 Starts: rows of data Name4 Starts: rows of data Name5 Starts: rows of data and so on, for up to 20-30 name blocks. What I'm trying to do is to get the number of rows between a cell containg 'Starts:' and the next name in column A to = 22, including 2 empty rows at the bottom. So if the number of 'data' rows is less than 20 (some blocks will have 0 rows of data), I need to pad it out with empty rows, but if it's more than 20 I need to delete the excess rows (starting at the bottom of the block). I then need to add an additional 2 empty rows so I end up with the following: A1: Name1 A2: Starts: A3 to A22: data/empty rows A23: empty row A24: empty row A25: Name2 A26: Starts: A27 to A46: data/empty rows A47: empty row A48: empty row A49: Name3 A50: Starts: A51 to A70: data/empty rows A71: empty row A72: empty row All the way down the sheet. I have got as far as figuring out how to add the 2 empty rows I need but have no idea where to head on the rest. Note that while I have only used column A in the example, entire rows need to be considered as other columns also contain data. -- Mark K ------------------------------------------------------------------------ Mark K's Profile: http://www.excelforum.com/member.php...o&userid=14117 View this thread: http://www.excelforum.com/showthread...hreadid=573244 |
Add/Delete rows based on count
OK, it appears dealing with this one all at once may be too much, s I'll start working on one thing at a time, coming back when I can' figure out how to do something. :) First off, how do I locate the first occurance of a cell in column "A containing the text "Starts:" ? Once that's done I will be able to us that cell as a start point for other actions and, hopefully, be able t do a lot on my own -- Mark ----------------------------------------------------------------------- Mark K's Profile: http://www.excelforum.com/member.php...fo&userid=1411 View this thread: http://www.excelforum.com/showthread.php?threadid=57324 |
Add/Delete rows based on count
Sorry I've been absent from the Internet for a couple of days...
To answer your question, I know there may be a better way of doing this, but try somethig simple like: Range("A1").Select Do Until Activecell.Value = "Start" Activecell.Offset(1,0).Select Loop Mark K wrote: OK, it appears dealing with this one all at once may be too much, so I'll start working on one thing at a time, coming back when I can't figure out how to do something. :) First off, how do I locate the first occurance of a cell in column "A" containing the text "Starts:" ? Once that's done I will be able to use that cell as a start point for other actions and, hopefully, be able to do a lot on my own. -- Mark K ------------------------------------------------------------------------ Mark K's Profile: http://www.excelforum.com/member.php...o&userid=14117 View this thread: http://www.excelforum.com/showthread...hreadid=573244 |
Add/Delete rows based on count
Thanks for that - quick and simple. So from there I could set it and do something like: Set sts = ActiveCell sts.Offset(5, 3).Select to select a cell 5 rows down, 3 columns over as the active cell. Bu sts would still equal the original cell correct? Sorry if this seems little simple, but I'm still only just learning -- Mark ----------------------------------------------------------------------- Mark K's Profile: http://www.excelforum.com/member.php...fo&userid=1411 View this thread: http://www.excelforum.com/showthread.php?threadid=57324 |
Add/Delete rows based on count
Yes, what you said works in limited situations. I'd try setting sts as
a string and using the activecell.address instead. Errors can arise if you're not specific about what you're working with and even using the Object or Variant type, things can get funky with what you suggest. But, for your specific example it works. I've been playing with a little code going back to your original question. This should do what you're describing, though it's a little messy: Public Sub AddRemoveRows() Dim dRows As Double, dLRow As Double, dSRow As Double Dim sCell As String Range("A1").Select Do Until Trim(LCase(ActiveCell.Value)) = LCase("starts:") ActiveCell.Offset(1, 0).Select Loop sCell = ActiveCell.Address dSRow = ActiveCell.Row - 2 Do Until ActiveCell.Value = Empty dRows = 0 dLRow = ActiveSheet.UsedRange.Rows.Count + dSRow 'go to next "Start: " in prep of editing rows 'or exit if last row is reached ActiveCell.Offset(1, 0).Select Do Until Trim(LCase(ActiveCell.Value)) = LCase("starts:") Or _ ActiveCell.Row dLRow ActiveCell.Offset(1, 0).Select dRows = dRows + 1 Loop 'add / delete rows depending on dRows Range(sCell).Select If dRows = 1 Then dRows = 0 Do Until dRows = 22 ActiveCell.Offset(dRows + 1, 0).EntireRow.Insert shift:=xlDown dRows = dRows + 1 Loop ElseIf dRows < 23 Then 'add rows Do Until dRows = 23 ActiveCell.Offset(dRows + 1, 0).EntireRow.Insert shift:=xlDown dRows = dRows + 1 Loop ElseIf dRows 23 Then 'delete rows Dim s As String Do Until dRows = 23 ActiveCell.Offset(dRows - 3, 0).EntireRow.Delete shift:=xlUp dRows = dRows - 1 Loop End If 'clear the last two of the 22 rows Range(ActiveCell.Offset(dRows - 2, 0).Address, ActiveCell.Offset(dRows - 1, 0).Address).EntireRow.ClearContents 'set the starting cell address to the next "Start: " sCell = ActiveCell.Offset(24, 0).Address Range(sCell).Select Loop Range("A1").Select End Sub Mark K wrote: Thanks for that - quick and simple. So from there I could set it and do something like: Set sts = ActiveCell sts.Offset(5, 3).Select to select a cell 5 rows down, 3 columns over as the active cell. But sts would still equal the original cell correct? Sorry if this seems a little simple, but I'm still only just learning. -- Mark K ------------------------------------------------------------------------ Mark K's Profile: http://www.excelforum.com/member.php...o&userid=14117 View this thread: http://www.excelforum.com/showthread...hreadid=573244 |
Add/Delete rows based on count
Thank you - almost perfect. Have come across 2 minor glitches. First on I fixed myself: Code ------------------- 'add rows Do Until dRows = 23 ActiveCell.Offset(dRows + 1, 0).EntireRow.Inser ------------------- The dRows +1 needed to be just dRows or it added the rows between th name and starts. The second glitch has me beat. It only affects the last block of data No matter how many rows of data are in the last set of records, al actions are performed above the last row. So if there are no data rows rows are added between the name and starts row. If there are data row then rows are added/deleted above the last row. Not a real problem if there's more than 20 rows to work with, but no good with less than 20 rows. I've tried to figure it out myself bu just don't have the knowledge. Any fix would be great -- Mark ----------------------------------------------------------------------- Mark K's Profile: http://www.excelforum.com/member.php...fo&userid=1411 View this thread: http://www.excelforum.com/showthread.php?threadid=57324 |
| All times are GMT +1. The time now is 05:08 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com