Clear contents macro

Hi
I have a macro that clears the contents of a sheet after I have
imported the data into a database.
This works fine but if the data is less then 175 rows it leaves
blank rows in the database.
How can I get round this.
Not very good with VBA would appreciate any help
Macro as below:-

Range("A2:N175").select
Selection.ClearContents
Range("A2").Select
Workbooks("MyBook1.xls").Close SaveChange:=True

What is the range of blank rows? What do you want to do with the blank
rows?

--
Regards,
Zack Barresse, aka firefytr
To email, remove NOSPAM


"Dave" <Dave@discussions.microsoft.com> wrote in message
news:20B7D2B7-7A7F-464C-BCDD-792B69D24F69@microsoft.com...
> Hi
> I have a macro that clears the contents of a sheet after I have
> imported the data into a database.
> This works fine but if the data is less then 175 rows it leaves
> blank rows in the database.
> How can I get round this.
> Not very good with VBA would appreciate any help
> Macro as below:-
>
> Range("A2:N175").select
> Selection.ClearContents
> Range("A2").Select
> Workbooks("MyBook1.xls").Close SaveChange:=True
>
>
>

I don't understand how an operation you perform on the worksheet after you
import data can affect the imported data but that might be my problem.

Seems to me that you don't want to hard code row 175 as an upper bound but
instead preface your code with a little loop that searches for the first
fully blank row that follows your poplulated rows. Does that sound OK or do
you still need to know how to write such a loop?

Luke

"Dave" <Dave@discussions.microsoft.com> wrote in message
news:20B7D2B7-7A7F-464C-BCDD-792B69D24F69@microsoft.com...
> Hi
> I have a macro that clears the contents of a sheet after I have
> imported the data into a database.
> This works fine but if the data is less then 175 rows it leaves
> blank rows in the database.
> How can I get round this.
> Not very good with VBA would appreciate any help
> Macro as below:-
>
> Range("A2:N175").select
> Selection.ClearContents
> Range("A2").Select
> Workbooks("MyBook1.xls").Close SaveChange:=True
>
>
>

Hi Luke
Thanks for your reply.
As I said I'm not very good with VBA,but I think I need a little loop to
search
for the first blank row after the populated rows, and can you help with the
coding please.
Thanks once again
Regards
Dave



"Luke Alcatel" wrote:

> I don't understand how an operation you perform on the worksheet after you
> import data can affect the imported data but that might be my problem.
>
> Seems to me that you don't want to hard code row 175 as an upper bound but
> instead preface your code with a little loop that searches for the first
> fully blank row that follows your poplulated rows. Does that sound OK or do
> you still need to know how to write such a loop?
>
> Luke
>
> "Dave" <Dave@discussions.microsoft.com> wrote in message
> news:20B7D2B7-7A7F-464C-BCDD-792B69D24F69@microsoft.com...
> > Hi
> > I have a macro that clears the contents of a sheet after I have
> > imported the data into a database.
> > This works fine but if the data is less then 175 rows it leaves
> > blank rows in the database.
> > How can I get round this.
> > Not very good with VBA would appreciate any help
> > Macro as below:-
> >
> > Range("A2:N175").select
> > Selection.ClearContents
> > Range("A2").Select
> > Workbooks("MyBook1.xls").Close SaveChange:=True
> >
> >
> >
>
>
>

Dave,
Here's an example:

Dim r As Integer, c As Integer, blank As Boolean
For r = 2 To 176
blank = True
For c = 1 To 10
If ActiveSheet.Cells(r, c).Value <> "" Then
blank = False
Exit For
End If
Next c
If blank Then Exit For
Next r

The first "for" loop sets the boundaries of the search from row 2 to row
176. We make the assumption that the row is blank (3rd line) and then the
second "for" loop tests this assumption by examining the first 10 columns of
the row. If any cell in the first 10 columns is not blank, the assumption
is disproved and we go on to examine the next row. At the end of this code,
if "blank" is True then "r" is the number of the first blank row. If
"blank" is False then we did not find any blank rows.

Luke

"Dave" <Dave@discussions.microsoft.com> wrote in message
news:1CA3BA68-C1CE-44E5-84F2-0D16FC5CE2E7@microsoft.com...
> Hi Luke
> Thanks for your reply.
> As I said I'm not very good with VBA,but I think I need a little loop to
> search
> for the first blank row after the populated rows, and can you help with
the
> coding please.
> Thanks once again
> Regards
> Dave
>
>
>
> "Luke Alcatel" wrote:
>
> > I don't understand how an operation you perform on the worksheet after
you
> > import data can affect the imported data but that might be my problem.
> >
> > Seems to me that you don't want to hard code row 175 as an upper bound
but
> > instead preface your code with a little loop that searches for the first
> > fully blank row that follows your poplulated rows. Does that sound OK
or do
> > you still need to know how to write such a loop?
> >
> > Luke
> >
> > "Dave" <Dave@discussions.microsoft.com> wrote in message
> > news:20B7D2B7-7A7F-464C-BCDD-792B69D24F69@microsoft.com...
> > > Hi
> > > I have a macro that clears the contents of a sheet after I have
> > > imported the data into a database.
> > > This works fine but if the data is less then 175 rows it leaves
> > > blank rows in the database.
> > > How can I get round this.
> > > Not very good with VBA would appreciate any help
> > > Macro as below:-
> > >
> > > Range("A2:N175").select
> > > Selection.ClearContents
> > > Range("A2").Select
> > > Workbooks("MyBook1.xls").Close SaveChange:=True
> > >
> > >
> > >
> >
> >
> >

Hi Luke,

Check out the conversation here:
http://www.puremis.net/excel/cgi-bin...num=1113337613 for a
better idea of Integer/Long type variables. Fwiw/fyi.

--
Regards,
Zack Barresse, aka firefytr
To email, remove NOSPAM


"Luke Alcatel" <nobody@nowhere.net> wrote in message
news:uFsvBLNVGHA.5288@TK2MSFTNGP14.phx.gbl...
> Dave,
> Here's an example:
>
> Dim r As Integer, c As Integer, blank As Boolean
> For r = 2 To 176
> blank = True
> For c = 1 To 10
> If ActiveSheet.Cells(r, c).Value <> "" Then
> blank = False
> Exit For
> End If
> Next c
> If blank Then Exit For
> Next r
>
> The first "for" loop sets the boundaries of the search from row 2 to row
> 176. We make the assumption that the row is blank (3rd line) and then the
> second "for" loop tests this assumption by examining the first 10 columns
> of
> the row. If any cell in the first 10 columns is not blank, the assumption
> is disproved and we go on to examine the next row. At the end of this
> code,
> if "blank" is True then "r" is the number of the first blank row. If
> "blank" is False then we did not find any blank rows.
>
> Luke
>
> "Dave" <Dave@discussions.microsoft.com> wrote in message
> news:1CA3BA68-C1CE-44E5-84F2-0D16FC5CE2E7@microsoft.com...
>> Hi Luke
>> Thanks for your reply.
>> As I said I'm not very good with VBA,but I think I need a little loop to
>> search
>> for the first blank row after the populated rows, and can you help with
> the
>> coding please.
>> Thanks once again
>> Regards
>> Dave
>>
>>
>>
>> "Luke Alcatel" wrote:
>>
>> > I don't understand how an operation you perform on the worksheet after
> you
>> > import data can affect the imported data but that might be my problem.
>> >
>> > Seems to me that you don't want to hard code row 175 as an upper bound
> but
>> > instead preface your code with a little loop that searches for the
>> > first
>> > fully blank row that follows your poplulated rows. Does that sound OK
> or do
>> > you still need to know how to write such a loop?
>> >
>> > Luke
>> >
>> > "Dave" <Dave@discussions.microsoft.com> wrote in message
>> > news:20B7D2B7-7A7F-464C-BCDD-792B69D24F69@microsoft.com...
>> > > Hi
>> > > I have a macro that clears the contents of a sheet after I have
>> > > imported the data into a database.
>> > > This works fine but if the data is less then 175 rows it leaves
>> > > blank rows in the database.
>> > > How can I get round this.
>> > > Not very good with VBA would appreciate any help
>> > > Macro as below:-
>> > >
>> > > Range("A2:N175").select
>> > > Selection.ClearContents
>> > > Range("A2").Select
>> > > Workbooks("MyBook1.xls").Close SaveChange:=True
>> > >
>> > >
>> > >
>> >
>> >
>> >
>
>

The reason I ask exactly what you're doing is that if you are looking for
blank rows, you don't need a loop, we can use AutoFilter, which is (in most
cases) faster than looping. If we can avoid loops, it's generally best.

--
Regards,
Zack Barresse, aka firefytr
To email, remove NOSPAM


"Luke Alcatel" <nobody@nobody.net> wrote in message
news:OKnONVFVGHA.2704@tk2msftngp13.phx.gbl...
>I don't understand how an operation you perform on the worksheet after you
> import data can affect the imported data but that might be my problem.
>
> Seems to me that you don't want to hard code row 175 as an upper bound but
> instead preface your code with a little loop that searches for the first
> fully blank row that follows your poplulated rows. Does that sound OK or
> do
> you still need to know how to write such a loop?
>
> Luke
>
> "Dave" <Dave@discussions.microsoft.com> wrote in message
> news:20B7D2B7-7A7F-464C-BCDD-792B69D24F69@microsoft.com...
>> Hi
>> I have a macro that clears the contents of a sheet after I have
>> imported the data into a database.
>> This works fine but if the data is less then 175 rows it leaves
>> blank rows in the database.
>> How can I get round this.
>> Not very good with VBA would appreciate any help
>> Macro as below:-
>>
>> Range("A2:N175").select
>> Selection.ClearContents
>> Range("A2").Select
>> Workbooks("MyBook1.xls").Close SaveChange:=True
>>
>>
>>
>
>

Fair enough. I did it with loops because 1) I'm a programmer rather than an
Excel expert and I've never heard of AutoFilter. I do a little VBA
programming when I have to, 2) After reading your mail I looked at
AutoFilter in VBA help. I have a feeling that at his knowledge level David
would be much better off studying and understanding my loop as opposed to
getting into the esoterica of filter object manipulation.

Luke

"Zack Barresse" <firefytrNO@SPAMvbaexpress.com> wrote in message
news:OjXoicNVGHA.4976@TK2MSFTNGP11.phx.gbl...
> The reason I ask exactly what you're doing is that if you are looking for
> blank rows, you don't need a loop, we can use AutoFilter, which is (in
most
> cases) faster than looping. If we can avoid loops, it's generally best.
>
> --
> Regards,
> Zack Barresse, aka firefytr
> To email, remove NOSPAM
>
>
> "Luke Alcatel" <nobody@nobody.net> wrote in message
> news:OKnONVFVGHA.2704@tk2msftngp13.phx.gbl...
> >I don't understand how an operation you perform on the worksheet after
you
> > import data can affect the imported data but that might be my problem.
> >
> > Seems to me that you don't want to hard code row 175 as an upper bound
but
> > instead preface your code with a little loop that searches for the first
> > fully blank row that follows your poplulated rows. Does that sound OK
or
> > do
> > you still need to know how to write such a loop?
> >
> > Luke
> >
> > "Dave" <Dave@discussions.microsoft.com> wrote in message
> > news:20B7D2B7-7A7F-464C-BCDD-792B69D24F69@microsoft.com...
> >> Hi
> >> I have a macro that clears the contents of a sheet after I have
> >> imported the data into a database.
> >> This works fine but if the data is less then 175 rows it leaves
> >> blank rows in the database.
> >> How can I get round this.
> >> Not very good with VBA would appreciate any help
> >> Macro as below:-
> >>
> >> Range("A2:N175").select
> >> Selection.ClearContents
> >> Range("A2").Select
> >> Workbooks("MyBook1.xls").Close SaveChange:=True
> >>
> >>
> >>
> >
> >
>
>

Hi Luke & Zack
Thanks for your input but i'm confused now, Loop or Autofilter?
All I want to know is, 1.Why when the Spreadsheet data is imported into
Access
it creates blank rows in a table if its less then 175 rows. 2. How can I
get round
this problem.
Thanks
Dave


"Luke Alcatel" wrote:

> Fair enough. I did it with loops because 1) I'm a programmer rather than an
> Excel expert and I've never heard of AutoFilter. I do a little VBA
> programming when I have to, 2) After reading your mail I looked at
> AutoFilter in VBA help. I have a feeling that at his knowledge level David
> would be much better off studying and understanding my loop as opposed to
> getting into the esoterica of filter object manipulation.
>
> Luke
>
> "Zack Barresse" <firefytrNO@SPAMvbaexpress.com> wrote in message
> news:OjXoicNVGHA.4976@TK2MSFTNGP11.phx.gbl...
> > The reason I ask exactly what you're doing is that if you are looking for
> > blank rows, you don't need a loop, we can use AutoFilter, which is (in
> most
> > cases) faster than looping. If we can avoid loops, it's generally best.
> >
> > --
> > Regards,
> > Zack Barresse, aka firefytr
> > To email, remove NOSPAM
> >
> >
> > "Luke Alcatel" <nobody@nobody.net> wrote in message
> > news:OKnONVFVGHA.2704@tk2msftngp13.phx.gbl...
> > >I don't understand how an operation you perform on the worksheet after
> you
> > > import data can affect the imported data but that might be my problem.
> > >
> > > Seems to me that you don't want to hard code row 175 as an upper bound
> but
> > > instead preface your code with a little loop that searches for the first
> > > fully blank row that follows your poplulated rows. Does that sound OK
> or
> > > do
> > > you still need to know how to write such a loop?
> > >
> > > Luke
> > >
> > > "Dave" <Dave@discussions.microsoft.com> wrote in message
> > > news:20B7D2B7-7A7F-464C-BCDD-792B69D24F69@microsoft.com...
> > >> Hi
> > >> I have a macro that clears the contents of a sheet after I have
> > >> imported the data into a database.
> > >> This works fine but if the data is less then 175 rows it leaves
> > >> blank rows in the database.
> > >> How can I get round this.
> > >> Not very good with VBA would appreciate any help
> > >> Macro as below:-
> > >>
> > >> Range("A2:N175").select
> > >> Selection.ClearContents
> > >> Range("A2").Select
> > >> Workbooks("MyBook1.xls").Close SaveChange:=True
> > >>
> > >>
> > >>
> > >
> > >
> >
> >
>
>
>

Neither one is that difficult once you know the Object Model. And lack of
knowledge has never stopped me from wanting a more robust/efficient
solution.

--
Regards,
Zack Barresse, aka firefytr
To email, remove NOSPAM


"Luke Alcatel" <nobody@nobody.net> wrote in message
news:OVUlQjOVGHA.4900@TK2MSFTNGP12.phx.gbl...
> Fair enough. I did it with loops because 1) I'm a programmer rather than
> an
> Excel expert and I've never heard of AutoFilter. I do a little VBA
> programming when I have to, 2) After reading your mail I looked at
> AutoFilter in VBA help. I have a feeling that at his knowledge level
> David
> would be much better off studying and understanding my loop as opposed to
> getting into the esoterica of filter object manipulation.
>
> Luke
>
> "Zack Barresse" <firefytrNO@SPAMvbaexpress.com> wrote in message
> news:OjXoicNVGHA.4976@TK2MSFTNGP11.phx.gbl...
>> The reason I ask exactly what you're doing is that if you are looking for
>> blank rows, you don't need a loop, we can use AutoFilter, which is (in
> most
>> cases) faster than looping. If we can avoid loops, it's generally best.
>>
>> --
>> Regards,
>> Zack Barresse, aka firefytr
>> To email, remove NOSPAM
>>
>>
>> "Luke Alcatel" <nobody@nobody.net> wrote in message
>> news:OKnONVFVGHA.2704@tk2msftngp13.phx.gbl...
>> >I don't understand how an operation you perform on the worksheet after
> you
>> > import data can affect the imported data but that might be my problem.
>> >
>> > Seems to me that you don't want to hard code row 175 as an upper bound
> but
>> > instead preface your code with a little loop that searches for the
>> > first
>> > fully blank row that follows your poplulated rows. Does that sound OK
> or
>> > do
>> > you still need to know how to write such a loop?
>> >
>> > Luke
>> >
>> > "Dave" <Dave@discussions.microsoft.com> wrote in message
>> > news:20B7D2B7-7A7F-464C-BCDD-792B69D24F69@microsoft.com...
>> >> Hi
>> >> I have a macro that clears the contents of a sheet after I have
>> >> imported the data into a database.
>> >> This works fine but if the data is less then 175 rows it leaves
>> >> blank rows in the database.
>> >> How can I get round this.
>> >> Not very good with VBA would appreciate any help
>> >> Macro as below:-
>> >>
>> >> Range("A2:N175").select
>> >> Selection.ClearContents
>> >> Range("A2").Select
>> >> Workbooks("MyBook1.xls").Close SaveChange:=True
>> >>
>> >>
>> >>
>> >
>> >
>>
>>
>
>

Dave,

Importing to Access is it's own animal, and Access can be quite cumbersome
if everything is not aligned up just right with values and data types, etc.
Are the blank rows there upon Import to Access or is Access adding these
blank rows on it's own somehow? I'm a little confused as to your order of
actions here. Can you post exactly what it is you are doing in specific
order?

--
Regards,
Zack Barresse, aka firefytr
To email, remove NOSPAM


"Dave" <Dave@discussions.microsoft.com> wrote in message
news:61BBE2F7-4F60-468D-8C22-6AF93F34E4B2@microsoft.com...
> Hi Luke & Zack
> Thanks for your input but i'm confused now, Loop or Autofilter?
> All I want to know is, 1.Why when the Spreadsheet data is imported into
> Access
> it creates blank rows in a table if its less then 175 rows. 2. How can I
> get round
> this problem.
> Thanks
> Dave
>
>
> "Luke Alcatel" wrote:
>
>> Fair enough. I did it with loops because 1) I'm a programmer rather than
>> an
>> Excel expert and I've never heard of AutoFilter. I do a little VBA
>> programming when I have to, 2) After reading your mail I looked at
>> AutoFilter in VBA help. I have a feeling that at his knowledge level
>> David
>> would be much better off studying and understanding my loop as opposed to
>> getting into the esoterica of filter object manipulation.
>>
>> Luke
>>
>> "Zack Barresse" <firefytrNO@SPAMvbaexpress.com> wrote in message
>> news:OjXoicNVGHA.4976@TK2MSFTNGP11.phx.gbl...
>> > The reason I ask exactly what you're doing is that if you are looking
>> > for
>> > blank rows, you don't need a loop, we can use AutoFilter, which is (in
>> most
>> > cases) faster than looping. If we can avoid loops, it's generally
>> > best.
>> >
>> > --
>> > Regards,
>> > Zack Barresse, aka firefytr
>> > To email, remove NOSPAM
>> >
>> >
>> > "Luke Alcatel" <nobody@nobody.net> wrote in message
>> > news:OKnONVFVGHA.2704@tk2msftngp13.phx.gbl...
>> > >I don't understand how an operation you perform on the worksheet after
>> you
>> > > import data can affect the imported data but that might be my
>> > > problem.
>> > >
>> > > Seems to me that you don't want to hard code row 175 as an upper
>> > > bound
>> but
>> > > instead preface your code with a little loop that searches for the
>> > > first
>> > > fully blank row that follows your poplulated rows. Does that sound
>> > > OK
>> or
>> > > do
>> > > you still need to know how to write such a loop?
>> > >
>> > > Luke
>> > >
>> > > "Dave" <Dave@discussions.microsoft.com> wrote in message
>> > > news:20B7D2B7-7A7F-464C-BCDD-792B69D24F69@microsoft.com...
>> > >> Hi
>> > >> I have a macro that clears the contents of a sheet after I have
>> > >> imported the data into a database.
>> > >> This works fine but if the data is less then 175 rows it leaves
>> > >> blank rows in the database.
>> > >> How can I get round this.
>> > >> Not very good with VBA would appreciate any help
>> > >> Macro as below:-
>> > >>
>> > >> Range("A2:N175").select
>> > >> Selection.ClearContents
>> > >> Range("A2").Select
>> > >> Workbooks("MyBook1.xls").Close SaveChange:=True
>> > >>
>> > >>
>> > >>
>> > >
>> > >
>> >
>> >
>>
>>
>>