Solved

excel autofill with various ranges

Posted on 2013-05-13
12
282 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
  • 6
  • 6
12 Comments
 
LVL 45

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 45

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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 

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 45

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
 

Author Comment

by:Harter
ID: 39166077
0
 
LVL 45

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 45

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 45

Accepted Solution

by:
aikimark earned 500 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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Technology opened people to different means of presenting information, but PowerPoint remains to be above competition. Know why PPT still works today.
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

777 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