469,271 Members | 934 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,271 developers. It's quick & easy.

Excel: How do I Skip Blank Cells

O
Action
Req.Feed Set Up

This is a column I have in Sheet1 (the sheet is not named) In this sheet I have or will have 10,000 records of names of clients and the status they hold with company
What i would like to have done is a everytime Reg.feed Set up is found in the Column O is that column A-W is copied and moved to sheet 2 (which is also not named). The promblem that i am having with the current code is that is does not skip blank cells data as to be in every cell of that column in order for it to keep moving down the column.

Here is the code

Expand|Select|Wrap|Line Numbers
  1. Sub Feed_set_up()
  2. 'Let's start at row 2. Row 1 has headers
  3. x = 3
  4. 'Start the loop
  5. Do While Cells(x, 15) <> ""
  6. 'Look for data with 'Feed set up'
  7. If Cells(x, 15) = "Req. Feed Set Up" Then
  8. 'copy the row if it contains '
  9. Worksheets("Sheet1").Rows(x).Copy
  10. 'Go to sheet2. Activate it. We want the data here
  11. Worksheets("Sheet2").Activate
  12. 'Find the first empty row in sheet2
  13. erow = Sheet2.Cells(Rows.Count, 15).End(xlUp).Offset(1, 0).Row
  14. 'Paste the data here
  15. ActiveSheet.Paste Destination:=Worksheets("Sheet2").Rows(erow)
  16. End If
  17. 'go to sheet1 again and actvate it
  18. Worksheets("Sheet1").Activate
  19. 'Loop through the other rows with data
  20. x = x + 1
  21. Loop
  22. End Sub
If there a way to solve this problem I really would be sooo thankful

Sandy
Oct 6 '11 #1

✓ answered by patjones

You need to change Dim intLastRow As Integer to Dim intLastRow As Long.

27 8798
patjones
931 Expert 512MB
It's a problem with the nature of the While condition. You need to modify the loop so that it will execute a pre-determined number of times (i.e. the number of rows that you have) rather than exiting at the first empty cell.

Do you in fact know ahead of time how many rows of data you have in the worksheet?

Pat
Oct 6 '11 #2
No i don’t know the number of rows. The Number of rows will change day to day because it is more used as a dump sheet to input the data then the active clients we want to transfer to sheet2 so if the words Req.feed set Up is add in the column we know this is a client that is active and need to be move to sheet for the next steps of working with the company. Is this going to be complicated code? Ughh i hope not. Thanks for helping me :-)
Oct 6 '11 #3
patjones
931 Expert 512MB
If the recognition of rows that need to be copied (e.g. Req.Feed), and the subsequent copying are both functioning as expected, it really becomes just a question of getting the loop to recognize when it has reached the end of the spreadsheet. It's something that I have to think about, again since my VBA experience is with Access.
Oct 6 '11 #4
Thanks for even giving it thought, I really im thankful I havent had no luck figuring this out and i never get answer back on any blogs that i try to get help from. If u think of the answer please let me know
Thanks
Oct 6 '11 #5
Rabbit
12,516 Expert Mod 8TB
If you need to find the last row in a sheet with data, you can use this
Expand|Select|Wrap|Line Numbers
  1. intLastRow = Sheet1.Cells.Find("*", Sheet1.Cells(1, 1), xlValues, xlPart, xlByRows, xlPrev).Row
Oct 6 '11 #6
beacon
579 512MB
Check out this link Sandy...it sounds like this will do what you need it to do.

http://www.techonthenet.com/excel/ma...for_string.php
Oct 6 '11 #7
patjones
931 Expert 512MB
Rabbit

If you need to find the last row in a sheet with data, you can use this
But that doesn't seem to work when blank cells are embedded in the data...

beacon

Check out this link Sandy...it sounds like this will do what you need it to do.
That also doesn't seem like it will work...the While/Wend loop will quit on the first cell that has Len( ) = 0.
Oct 6 '11 #8
NeoPa
32,171 Expert Mod 16PB
While I'm looking at some stuff for you Sandy, why don't you check out some of these links :
When Posting (VBA or SQL) Code
Please Use Appropriate Titles for New Threads

PS. Unless there are any objections I'll reset the Best Answer post as it seems the OP got somewhat confused (Pat does a lot of good stuff but that's mainly a clarification question).
Oct 6 '11 #9
Rabbit
12,516 Expert Mod 8TB
It should work to find the very last row in a sheet where at least one cell has data. It shouldn't matter if any intervening rows have blank cells or rows.
Oct 6 '11 #10
patjones
931 Expert 512MB
Rabbit

It should work to find the very last row in a sheet where at least one cell has data. It shouldn't matter if any intervening rows have blank cells or rows.
I tried it out with text in cells A1, A2, A4, A5, A6 and leaving A3 blank. The code returned that there were only two entries in the column...
Oct 6 '11 #11
NeoPa
32,171 Expert Mod 16PB
This code should help. It assumes that Sheet1 & Sheet2 are both already available in the spreadsheet and it works on the basis of copying values across exclusively as experience tells me this is what's most often required when saving stuff away :
Expand|Select|Wrap|Line Numbers
  1. Public Sub Feed_Setup()
  2.     Dim lngRow As Long, lngRow1 As Long, lngRow2 As Long
  3.  
  4.     With Worksheets("Sheet2")
  5.         Call .Select
  6.         Call .Activate
  7.         lngRow2 = ActiveCell.SpecialCells(xlLastCell).Row + 1
  8.         lngRow2 = Range("O" & lngRow2).End(xlUp).Row + 1
  9.     End With
  10.     With Worksheets("Sheet1")
  11.         lngRow1 = .ActiveCell.SpecialCells(xlLastCell).Row + 1
  12.         lngRow1 = .Range("O" & lngRow2).End(xlUp).Row 
  13.         For lngRow = 2 To lngRow1
  14.             If .Range("O" & lngRow) = "Req. Feed Set Up" Then _
  15.                 Range("A" & lngRow2 & ":W" & lngRow2) = _
  16.                     .Range("A" & lngRow & ":W" & lngRow).Value
  17.             lngRow2 = lngRow2 + 1
  18.         Next lngRow
  19.     End With
  20. End Sub
Oct 6 '11 #12
patjones
931 Expert 512MB
After doing a little research, I found that one can apparently determine the last row by:

Expand|Select|Wrap|Line Numbers
  1. intLastRow = ActiveSheet.UsedRange.Rows.Count

So perhaps your code can be just slightly modified to:

Expand|Select|Wrap|Line Numbers
  1. Sub Feed_set_up()
  2.  
  3. Dim intLastRow As Integer
  4. intLastRow = ActiveSheet.UsedRange.Rows.Count
  5.  
  6. 'Let's start at row 2. Row 1 has headers
  7. x = 3
  8.  
  9. 'Start the loop
  10. Do While x<= intLastRow 
    'Look for data with 'Feed set up'
  11.     If Cells(x, 15) = "Req. Feed Set Up" Then
  12.     'copy the row if it contains '
  13.     Worksheets("Sheet1").Rows(x).Copy
  14.     'Go to sheet2. Activate it. We want the data here
  15.     Worksheets("Sheet2").Activate
  16.     'Find the first empty row in sheet2
  17.     erow = Sheet2.Cells(Rows.Count, 15).End(xlUp).Offset(1, 0).Row
  18.     'Paste the data here
  19.     ActiveSheet.Paste Destination:=Worksheets("Sheet2").Rows(erow)
  20.     End If
  21.     'go to sheet1 again and actvate it
  22.     Worksheets("Sheet1").Activate
  23.     'Loop through the other rows with data
  24.     x = x + 1
  25. Loop
  26. End Sub
Oct 6 '11 #13
Rabbit
12,516 Expert Mod 8TB
That's odd, I've used it before and I've never had a problem with it. It turns out though, if you use that, and then use
Expand|Select|Wrap|Line Numbers
  1. Sheet1.Cells.FindPrevious.Row
it should return the correct row.

@NeoPa, I've seen that before, using the xlLastCell. But I heard it was erratic because Excel doesn't store the last cell well.
Oct 6 '11 #14
Thanks You soo much I got it to work a few times but now it say Run-time Error '6' Over flow
@Neopa thanks for you help Much Apperciated...
Oct 6 '11 #15
The Highlighted error Is THe b{intLastRow = ActiveSheet.UsedRange.Rows.Count}
Oct 6 '11 #16
Stewart Ross
2,545 Expert Mod 2GB
Variable intLastRow in the VBA code is defined as an integer value. Integer types are stored in two bytes and are limited to a max positive value of 32,767. Excel can have up to 65,536 rows (Excel 2003 and previous versions), or 1,048,576 rows (Excel 2007 and beyond).

To cure the overflow change the type of intLastRow to Long, which is stored in 4 bytes and has a maximum value of 2,147,483,647, which is well beyond the number of rows Excel can store.

Strictly, its name should also change to lngLastRow to reflect the type change, but in this case I'd just change the type alone for simplicity.

-Stewart
Oct 6 '11 #17
What do i change to replace it? Steward. By the ways thats for your help... :-)
Oct 6 '11 #18
patjones
931 Expert 512MB
You need to change Dim intLastRow As Integer to Dim intLastRow As Long.
Oct 6 '11 #19
Thanks every one so Much It is working now after I activate it copies the rows then goes into not responing mode. I know it because it is mostly like check every cell in the column. But again thanks Guys
Oct 6 '11 #20
NeoPa
32,171 Expert Mod 16PB
Rabbit:
@NeoPa, I've seen that before, using the xlLastCell. But I heard it was erratic because Excel doesn't store the last cell well.
That's a surprise to me. I've been using it for ever and never seen any issues. Can you be more specific as to when/where to expect problems?

UsedRange.Rows.Count is actually not an indication of the last row. Typically, most worksheets start at the top so it it will have a value similar to that, but it really is just a count of the rows used. To see an illustration of this enter a value in a new worksheet in the cell N15. The value of UsedRange.Rows.Count shows as 1. Clearly that won't effect many worksheets, but you should be aware of it at least. There are various reasons why some worksheets don't start in row 1.

@Sandy
I'm curious. Did you have problems with the code I posted in post #12?
Oct 6 '11 #21
The code you gave me Neopa worked perfect for 3 times then it would say Run-time Error: overflow but i got it to work okay for now and i am very thankful this code has been a pain... But thanks to every help i got to run!
Sandy
Oct 6 '11 #22
Rabbit
12,516 Expert Mod 8TB
Put data in rows 1 to 3, last cell is 3. Delete row 3, last cell is still 3. Put italics on row 5. Last cell is now row 5.
Oct 6 '11 #23
NeoPa
32,171 Expert Mod 16PB
NeoPa:
That's a surprise to me. I've been using it for ever and never seen any issues. Can you be more specific as to when/where to expect problems?
That's to say I do know of an issue, but not one that would cause this code to fail to work as expected. I'll explain what I know and you can tell me if it's what you were thinking of.

When changes are made to a worksheet the value for SpecialCells(xlLastCell) reflects any increase in the spread of the data, but doesn't reflect any deletions until the workbook is saved. This can be seen by pressing Ctrl-End from the keyboard at any time. That takes you to the cell pointed to by SpecialCells(xlLastCell).
Oct 6 '11 #24
NeoPa
32,171 Expert Mod 16PB
Rabbit:
Put data in rows 1 to 3, last cell is 3. Delete row 3, last cell is still 3. Put italics on row 5. Last cell is now row 5.
I guess I should have refreshed first :-(

What you say is exactly as it should work, apart from the failure to register the deleted rows until it's saved. Certainly that behaviour was factored into the code.

@Sandy
I'm surprised you say it overflowed. I can see why you may not want to worry about chasing that down as you already have some working code from Pat, but I admit I'm confused that anything there could overflow. I was careful only to use Long variables for Rows.

Nevermind eh. While you're reading though, and as you have got yourself a working answer from Pat's post #13, it may be a good time to set that post as the Best Answer for the thread.
Oct 6 '11 #25
Stewart Ross
2,545 Expert Mod 2GB
@NeoPa: One of the less useful features of SpecialCells(xlLastCell) is that it counts pre-formatted but blank cells. So, if you develop a template with, say, conditional formatting applied to rows 1 to 100 columns A to F, then clear the contents, SpecialCells(xlLastCell) will always return F100 as the last cell used.

As long as specific formats are not applied to unfilled cells I have found SpecialCells(xlLastCell) completely reliable.

-Stewart
Oct 6 '11 #26
patjones
931 Expert 512MB
It's really just the code originally posted with a change in the While condition. It sounds like there still might be an overflow issue though.
Oct 6 '11 #27
NeoPa
32,171 Expert Mod 16PB
@Stewart.
I don't believe a cell can ever be considered blank if it's formatted. That's the difference between the Clear and the ClearContents methods of Range. It's absolutely correct for SpecialCells(xlLastCell) to return the cell F100 in that situation. UsedRange works differently in that it reflects only those cells with values (visible or otherwise - by which I'm not referring to hidden cells but to those with empty strings or just spaces). The Address() method can be used to return the last row used if processed by Split(.Address, "$")(5).

In the case of finding the last used row of data I've always found the following concept (Used in my suggested code) to work perfectly (If it finds any valueless cells then this is minute as a problem and easily handled by the Ctrl-Up) :
  1. Ctrl-End ==> .SpecialCells(xlLastCell).
  2. Left x X columns ==> Find the cell in this row but in the column we're interested in (which we know to be populated for valid rows).
  3. Ctrl-Up ==> .End(xlUp)
Oct 6 '11 #28

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

3 posts views Thread by James Wong | last post: by
1 post views Thread by Sean Howard | last post: by
4 posts views Thread by Keith Wilby | last post: by
4 posts views Thread by christianlott1 | last post: by
reply views Thread by Nadirsha Muhammed | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.