?
Solved

excel autofill with various ranges

Posted on 2013-05-13
12
Medium Priority
?
296 Views
Last Modified: 2013-05-20
Hello,
I have a question on how to possibly make this excel spreadsheet work in access DB.
I have to autofill the names of the companies with the times entries before importing the spreadsheet into access for further
The issue is that the range for autofill will change because the spreadsheet is exported monthly from QuickBooks.
Attached is the sample of the spreadsheet. Any help is appreciated.
QBTime-sample.xlsx
0
Comment
Question by:Harter
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 6
12 Comments
 
LVL 46

Expert Comment

by:aikimark
ID: 39164578
Do you need column C data autofilled down as well as column B data?
0
 

Author Comment

by:Harter
ID: 39164644
no, I was just going to delete B. I need to associate each row of data with date, name and duration to the data in column C, so that when I run the import macro into Access I can work with data. Essentially the access database will keep track of the duration and dates per client name. The user will run reports based on the date to see the total duration (basically time)
that person spent working with a particular client.
thanks
0
 
LVL 46

Expert Comment

by:aikimark
ID: 39164677
This seems to do what you describe. I am assuming that you will eventually eliminate the Total rows (pre or post import).

Option Explicit

Public Sub Q_28126449()
    Dim rng As Range
    Dim rngEnd As Range
    Dim rngCol As Range
    Dim boolStop As Boolean
    For Each rngCol In ActiveSheet.Range("B:B").Columns
        Set rng = rngCol.Cells(1, 1)
        Set rng = rng.End(xlDown)
        boolStop = False
        Do
            Set rngEnd = rng.End(xlDown)
            If rngEnd.Row = ActiveSheet.Rows.Count Then
                Set rngEnd = ActiveSheet.Cells(rngEnd.Row, 11).End(xlUp).Offset(0, -(11 - rngCol.Column))
                boolStop = True
            Else
                Set rngEnd = rngEnd.Offset(-1)
            End If
            If rng.Row = rngEnd.Row Then
            Else
                ActiveSheet.Range(rng, rngEnd).FillDown
            End If
            Set rng = rngEnd.Offset(1)
        Loop Until boolStop
    Next
End Sub

Open in new window

0
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 

Author Comment

by:Harter
ID: 39166046
I copied your code into VB in excel and ran it and it didnt do anything. Obviously i am not running it correctly. Should this code be put into AutoExec module into the spreadsheet, but the spreadsheet will be overwritten when the user exports the data.
Any suggestions are appreciated.
thanks
0
 
LVL 46

Expert Comment

by:aikimark
ID: 39166056
In order to run this in a saved/opened workbook, you will need to save the workbook in xlsm format.
0
 
LVL 46

Expert Comment

by:aikimark
ID: 39166231
Change the range in the code to "C:C"

The notebook you posted with your question had this data in column B.  The code is acting on column B.
0
 

Author Comment

by:Harter
ID: 39166475
The code deleted all the data in Column C.
0
 
LVL 46

Expert Comment

by:aikimark
ID: 39166972
Ah.  I hadn't notice before that the numeric values have been moved from column K to column L.  Please try this version of the code.
Option Explicit

Public Sub Q_28126449()
    Dim rng As Range
    Dim rngEnd As Range
    Dim rngCol As Range
    Dim boolStop As Boolean
    For Each rngCol In ActiveSheet.Range("B:B").Columns
        Set rng = rngCol.Cells(1, 1)
        Set rng = rng.End(xlDown)
        boolStop = False
        Do
            Set rngEnd = rng.End(xlDown)
            If rngEnd.Row = ActiveSheet.Rows.Count Then
                Set rngEnd = ActiveSheet.Cells(rngEnd.Row, 12).End(xlUp).Offset(0, -(12 - rngCol.Column))
                boolStop = True
            Else
                Set rngEnd = rngEnd.Offset(-1)
            End If
            If rng.Row = rngEnd.Row Then
            Else
                ActiveSheet.Range(rng, rngEnd).FillDown
            End If
            Set rng = rngEnd.Offset(1)
        Loop Until boolStop
    Next
End Sub

Open in new window

0
 

Author Comment

by:Harter
ID: 39180925
sorry for the late response. The code works great, however, how do i delete the total row? I want to keep the total row where 'total' client is in column B but not in C. If that makes sense?
here is the pic.
thank you so much for your assistance.
keep-total-in-column-B.JPG
0
 
LVL 46

Accepted Solution

by:
aikimark earned 2000 total points
ID: 39181412
The simplest way is to put a filter on the columns and filter for the word "total".  Then delete those rows.  Since you are already manually editing the worksheet, this would be one additional step in the process.

You might also tweak the code that I posted to iterate through the cells in the column, looking for "total" and deleting the entire row.  If you do this, loop from the last row to the first row, since deletions can mess up the looping process.
0
 

Author Closing Comment

by:Harter
ID: 39181660
Awesome, thanks for your help.
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
I was prompted to write this article after the recent World-Wide Ransomware outbreak. For years now, System Administrators around the world have used the excuse of "Waiting a Bit" before applying Security Patch Updates. This type of reasoning to me …
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question