473,883 Members | 1,847 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Excel: How do I Skip Blank Cells

88 New Member
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
27 9150
patjones
931 Recognized Expert Contributor
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
sandy armstrong
88 New Member
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 Recognized Expert Contributor
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
sandy armstrong
88 New Member
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 Recognized Expert Moderator MVP
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 Contributor
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 Recognized Expert Contributor
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,584 Recognized Expert Moderator MVP
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 Recognized Expert Moderator MVP
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

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

Similar topics

0
2945
by: ImraneA | last post by:
Hi there Have a database, where front-end interface allows user to select a ms access database. From there, standard tables are linked. Routine, that creates a spreadsheet, for each table a separate sheet within spreadsheet is created. Particular fields are selected. User requires fields to be auto-fitted. Problem, is that some tables have more than 1200> rows. Code generates error message. Is there a way of getting around this...
1
11670
by: tkaleb | last post by:
I have to create output file in a text, MS Access, MS Excel and .dbf format from C# Win/ADO.NET application. Data are collected in DataSet and there is no problem to make text file. However, I have to create a new output files (tables with defined fields) in other 3 formats, and to fill them with data from DataSet. I created Excel output with ComponentOne's trial Excel component, but it is a temporary solution. Also I have unusual CP (1250...
3
2974
by: Omar | last post by:
Hi Developers, I am trying to access an Excel data file through a VB.Net application. I have the following code: =================================== VB.Net Code =================== Dim sSqlString = "" Dim rCount As Integer = 0 Dim sDataSet As New DataSet() sSqlString = "Select * From "
3
10756
by: James Wong | last post by:
Dear all, I have an old VB6 application which can create and access Excel object. The basic definition statements are as follows: Dim appExcel As Object Dim wkb1 As Excel.Workbook Dim wks1 As Excel.Worksheet Set appExcel = New
1
2804
by: Sean Howard | last post by:
I have an Access database/procedure that exports data to an Excel spreadsheet and opens that spreadsheet using automation I need to change the current directory/drive in Excel to be the same as that of the spreadsheet I've created so that the user is in the correct place in Excel when the Access procedure finishes. I know of ChDrive() and ChDir() but how do I manipulate these functions in my Excel Application object from Access?
1
2768
by: TechnoPup | last post by:
Greetings, I am very new to working with databases, and I am not sure how to go about structuring the query I need. What I have is an Access database with approx. 400,000 records in 5 fields. The fields are owner, filename, size, path, modified date. What I would like to do is take the field "filename", and come up with a count of the different file types listed in the records (XLS, PPT, DOC, etc...) as well as a sum of the bytes that...
4
2769
by: Keith Wilby | last post by:
How controllable from Access VBA is Excel? I'm currently using automation to dump 2 columns of data into an Excel spreadsheet so that the end user can create a line graph based on it. Could the line graph be created from Access as part of the automation process? Is it also possible to rename the Excel worksheets from Access? Thanks. Keith.
4
15393
by: christianlott1 | last post by:
I've linked an excel worksheet as an access table. The values appear but it won't allow me to change any of the values. To test I've provided a fresh blank workbook and same problem. I've done this on other computers and it's worked fine. Using: Win 2000 SP4
4
1139
by: Nisio | last post by:
Guys Here's my prob. I work with a group og non-techie guys who are non-PC literate but need to produce exacting detailed reports on a regular basis. They are living proof of a little knowledge is a dangerous thing etc.. What is currnetly happening is the open fully editable doc's and SS's which they edit incorrectly and then issue to customers in an appauling state. What I need to do is limit the docs and SS's to forms allowing...
0
1549
by: Nadirsha Muhammed | last post by:
How to access excel function wizard through C#
0
9944
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, weíll explore What is ONU, What Is Router, ONU & Routerís main usage, and What is the difference between ONU and Router. Letís take a closer look ! Part I. Meaning of...
0
9796
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
11152
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10753
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
10859
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
5804
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
1
4620
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4225
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3239
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.