Autofilter on a currentregion resized
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}
I am trying to optimize the update processing time of Excel file from an Access application so I want to apply autofilter on a resized currentRegion.
This code below is a part of a Access routine which add / update an Excel file with a Access recordset data.
With xlsWkSheet
.AutoFilterMode = True
lXlsRowNumber = .Cells(.Rows.Count, .Range("ColRef").Column).End(xlUp).Row
Set oXlsCurrentRegion=.Range("A1").CurrentRegion.Resize(RowSize:=lXlsRowNumber)
With oXlsCurrentRegion
.AutoFilterMode = True
lIdxCol = .Range("ColCrit1").Column
.AutoFilter Field:=lIdxCol, Criteria1:=IIf(Nz(oRecSet![ValueAccess], "") = "", "=", oRecSet![ValueAccess])
End If
Set xlsRangeAutoFilter = .SpecialCells(xlCellTypeVisible)
End With
When "=" is applied on autofilter because oRecSet![ValueAccess] is null, on left top corner of Excel file, it's displayed
0 Of 1047585 records found.
When I apply autofilter on blank file with only headers I expected « 0 of 1 records founds »
I hoped that the filter was only applied on oXlsCurrentRegion areas but apparently not!
Is there a way to apply blank criteria only on XlsCurrentRegion areas?
excel vba range autofilter
edited Jan 6 at 13:08
Community♦
11
asked Jan 4 at 17:18
informerinformer
236
add a comment |
I am trying to optimize the update processing time of Excel file from an Access application so I want to apply autofilter on a resized currentRegion.
This code below is a part of a Access routine which add / update an Excel file with a Access recordset data.
With xlsWkSheet
.AutoFilterMode = True
lXlsRowNumber = .Cells(.Rows.Count, .Range("ColRef").Column).End(xlUp).Row
Set oXlsCurrentRegion=.Range("A1").CurrentRegion.Resize(RowSize:=lXlsRowNumber)
With oXlsCurrentRegion
.AutoFilterMode = True
lIdxCol = .Range("ColCrit1").Column
.AutoFilter Field:=lIdxCol, Criteria1:=IIf(Nz(oRecSet![ValueAccess], "") = "", "=", oRecSet![ValueAccess])
End If
Set xlsRangeAutoFilter = .SpecialCells(xlCellTypeVisible)
End With
When "=" is applied on autofilter because oRecSet![ValueAccess] is null, on left top corner of Excel file, it's displayed
0 Of 1047585 records found.
When I apply autofilter on blank file with only headers I expected « 0 of 1 records founds »
I hoped that the filter was only applied on oXlsCurrentRegion areas but apparently not!
Is there a way to apply blank criteria only on XlsCurrentRegion areas?
excel vba range autofilter
edited Jan 6 at 13:08
Community♦
11
asked Jan 4 at 17:18
informerinformer
236
First things first, checkoXlsCurrentRegionwithMsgBox oXlsCurrentRegion.Address
– chris neilsen
Jan 4 at 20:54
And maybe the firstAutoFilterModeshould beFalse
– chris neilsen
Jan 4 at 20:56
Hi Chris, thanks for your help. I have to wait next Monday for checking address and changing for False. I make a feedback as soon as possible
– informer
Jan 5 at 7:12
Hi Chris, you're right about autofilterMode. Its value must be False! Thanks a lot for your help
– informer
Jan 6 at 17:15
add a comment |
I am trying to optimize the update processing time of Excel file from an Access application so I want to apply autofilter on a resized currentRegion.
This code below is a part of a Access routine which add / update an Excel file with a Access recordset data.
With xlsWkSheet
.AutoFilterMode = True
lXlsRowNumber = .Cells(.Rows.Count, .Range("ColRef").Column).End(xlUp).Row
Set oXlsCurrentRegion=.Range("A1").CurrentRegion.Resize(RowSize:=lXlsRowNumber)
With oXlsCurrentRegion
.AutoFilterMode = True
lIdxCol = .Range("ColCrit1").Column
.AutoFilter Field:=lIdxCol, Criteria1:=IIf(Nz(oRecSet![ValueAccess], "") = "", "=", oRecSet![ValueAccess])
End If
Set xlsRangeAutoFilter = .SpecialCells(xlCellTypeVisible)
End With
When "=" is applied on autofilter because oRecSet![ValueAccess] is null, on left top corner of Excel file, it's displayed
0 Of 1047585 records found.
When I apply autofilter on blank file with only headers I expected « 0 of 1 records founds »
I hoped that the filter was only applied on oXlsCurrentRegion areas but apparently not!
Is there a way to apply blank criteria only on XlsCurrentRegion areas?
excel vba range autofilter
edited Jan 6 at 13:08
Community♦
11
asked Jan 4 at 17:18
informerinformer
236
I am trying to optimize the update processing time of Excel file from an Access application so I want to apply autofilter on a resized currentRegion.
This code below is a part of a Access routine which add / update an Excel file with a Access recordset data.
With xlsWkSheet
.AutoFilterMode = True
lXlsRowNumber = .Cells(.Rows.Count, .Range("ColRef").Column).End(xlUp).Row
Set oXlsCurrentRegion=.Range("A1").CurrentRegion.Resize(RowSize:=lXlsRowNumber)
With oXlsCurrentRegion
.AutoFilterMode = True
lIdxCol = .Range("ColCrit1").Column
.AutoFilter Field:=lIdxCol, Criteria1:=IIf(Nz(oRecSet![ValueAccess], "") = "", "=", oRecSet![ValueAccess])
End If
Set xlsRangeAutoFilter = .SpecialCells(xlCellTypeVisible)
End With
When "=" is applied on autofilter because oRecSet![ValueAccess] is null, on left top corner of Excel file, it's displayed
0 Of 1047585 records found.
When I apply autofilter on blank file with only headers I expected « 0 of 1 records founds »
I hoped that the filter was only applied on oXlsCurrentRegion areas but apparently not!
Is there a way to apply blank criteria only on XlsCurrentRegion areas?
excel vba range autofilter
excel vba range autofilter
edited Jan 6 at 13:08
Community♦
11
asked Jan 4 at 17:18
informerinformer
236
edited Jan 6 at 13:08
Community♦
11
asked Jan 4 at 17:18
informerinformer
236
edited Jan 6 at 13:08
Community♦
11
edited Jan 6 at 13:08
Community♦
11
edited Jan 6 at 13:08
Community♦
11
11
asked Jan 4 at 17:18
informerinformer
236
asked Jan 4 at 17:18
informerinformer
236
asked Jan 4 at 17:18
informerinformer
236
236
First things first, checkoXlsCurrentRegionwithMsgBox oXlsCurrentRegion.Address
– chris neilsen
Jan 4 at 20:54
And maybe the firstAutoFilterModeshould beFalse
– chris neilsen
Jan 4 at 20:56
Hi Chris, thanks for your help. I have to wait next Monday for checking address and changing for False. I make a feedback as soon as possible
– informer
Jan 5 at 7:12
Hi Chris, you're right about autofilterMode. Its value must be False! Thanks a lot for your help
– informer
Jan 6 at 17:15
add a comment |
First things first, checkoXlsCurrentRegionwithMsgBox oXlsCurrentRegion.Address
– chris neilsen
Jan 4 at 20:54
And maybe the firstAutoFilterModeshould beFalse
– chris neilsen
Jan 4 at 20:56
Hi Chris, thanks for your help. I have to wait next Monday for checking address and changing for False. I make a feedback as soon as possible
– informer
Jan 5 at 7:12
Hi Chris, you're right about autofilterMode. Its value must be False! Thanks a lot for your help
– informer
Jan 6 at 17:15
First things first, check
oXlsCurrentRegion with MsgBox oXlsCurrentRegion.Address– chris neilsen
Jan 4 at 20:54
First things first, check
oXlsCurrentRegion with MsgBox oXlsCurrentRegion.Address– chris neilsen
Jan 4 at 20:54
And maybe the first
AutoFilterMode should be False– chris neilsen
Jan 4 at 20:56
And maybe the first
AutoFilterMode should be False– chris neilsen
Jan 4 at 20:56
Hi Chris, thanks for your help. I have to wait next Monday for checking address and changing for False. I make a feedback as soon as possible
– informer
Jan 5 at 7:12
Hi Chris, thanks for your help. I have to wait next Monday for checking address and changing for False. I make a feedback as soon as possible
– informer
Jan 5 at 7:12
Hi Chris, you're right about autofilterMode. Its value must be False! Thanks a lot for your help
– informer
Jan 6 at 17:15
Hi Chris, you're right about autofilterMode. Its value must be False! Thanks a lot for your help
– informer
Jan 6 at 17:15
add a comment |
2 Answers
2
active
oldest
votes
I tested this code on a Excel file and it works perfectly
Private Sub CommandButton1_Click()
With ActiveSheet
.FilterMode = False
.AutoFilterMode = False
Set oRange = .Range("A2:E8")
With oRange
.AutoFilter Field:=5, Criteria1:="="
End With
End With
End Sub
answered Jan 6 at 10:37
informerinformer
236
add a comment |
On The Ultimate Guide to Excel Filters with VBA Macros – AutoFilter Method we can read this code below which seems to be the solution of my problem
Sub AutoFilter_Range()
'AutoFilter is a member of the Range object
'Reference the entire range that the filters are applied to
'AutoFilter turns filters on/off when no parameters are specified.
Sheet1.Range("B3:G1000").AutoFilter
'Fully qualified reference starting at Workbook level
ThisWorkbook.Worksheets("AutoFilter Guide").Range("B3:G1000").AutoFilter
End Sub
And as we can read on How to use AutoFilters in Excel VBA Macros
AutoFilterMode and FilterMode must be set with False value
answered Jan 5 at 21:24
informerinformer
236
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54043409%2fautofilter-on-a-currentregion-resized%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
I tested this code on a Excel file and it works perfectly
Private Sub CommandButton1_Click()
With ActiveSheet
.FilterMode = False
.AutoFilterMode = False
Set oRange = .Range("A2:E8")
With oRange
.AutoFilter Field:=5, Criteria1:="="
End With
End With
End Sub
answered Jan 6 at 10:37
informerinformer
236
add a comment |
I tested this code on a Excel file and it works perfectly
Private Sub CommandButton1_Click()
With ActiveSheet
.FilterMode = False
.AutoFilterMode = False
Set oRange = .Range("A2:E8")
With oRange
.AutoFilter Field:=5, Criteria1:="="
End With
End With
End Sub
answered Jan 6 at 10:37
informerinformer
236
add a comment |
I tested this code on a Excel file and it works perfectly
Private Sub CommandButton1_Click()
With ActiveSheet
.FilterMode = False
.AutoFilterMode = False
Set oRange = .Range("A2:E8")
With oRange
.AutoFilter Field:=5, Criteria1:="="
End With
End With
End Sub
answered Jan 6 at 10:37
informerinformer
236
I tested this code on a Excel file and it works perfectly
Private Sub CommandButton1_Click()
With ActiveSheet
.FilterMode = False
.AutoFilterMode = False
Set oRange = .Range("A2:E8")
With oRange
.AutoFilter Field:=5, Criteria1:="="
End With
End With
End Sub
answered Jan 6 at 10:37
informerinformer
236
answered Jan 6 at 10:37
informerinformer
236
answered Jan 6 at 10:37
informerinformer
236
answered Jan 6 at 10:37
informerinformer
236
236
add a comment |
add a comment |
On The Ultimate Guide to Excel Filters with VBA Macros – AutoFilter Method we can read this code below which seems to be the solution of my problem
Sub AutoFilter_Range()
'AutoFilter is a member of the Range object
'Reference the entire range that the filters are applied to
'AutoFilter turns filters on/off when no parameters are specified.
Sheet1.Range("B3:G1000").AutoFilter
'Fully qualified reference starting at Workbook level
ThisWorkbook.Worksheets("AutoFilter Guide").Range("B3:G1000").AutoFilter
End Sub
And as we can read on How to use AutoFilters in Excel VBA Macros
AutoFilterMode and FilterMode must be set with False value
answered Jan 5 at 21:24
informerinformer
236
add a comment |
On The Ultimate Guide to Excel Filters with VBA Macros – AutoFilter Method we can read this code below which seems to be the solution of my problem
Sub AutoFilter_Range()
'AutoFilter is a member of the Range object
'Reference the entire range that the filters are applied to
'AutoFilter turns filters on/off when no parameters are specified.
Sheet1.Range("B3:G1000").AutoFilter
'Fully qualified reference starting at Workbook level
ThisWorkbook.Worksheets("AutoFilter Guide").Range("B3:G1000").AutoFilter
End Sub
And as we can read on How to use AutoFilters in Excel VBA Macros
AutoFilterMode and FilterMode must be set with False value
answered Jan 5 at 21:24
informerinformer
236
add a comment |
On The Ultimate Guide to Excel Filters with VBA Macros – AutoFilter Method we can read this code below which seems to be the solution of my problem
Sub AutoFilter_Range()
'AutoFilter is a member of the Range object
'Reference the entire range that the filters are applied to
'AutoFilter turns filters on/off when no parameters are specified.
Sheet1.Range("B3:G1000").AutoFilter
'Fully qualified reference starting at Workbook level
ThisWorkbook.Worksheets("AutoFilter Guide").Range("B3:G1000").AutoFilter
End Sub
And as we can read on How to use AutoFilters in Excel VBA Macros
AutoFilterMode and FilterMode must be set with False value
answered Jan 5 at 21:24
informerinformer
236
On The Ultimate Guide to Excel Filters with VBA Macros – AutoFilter Method we can read this code below which seems to be the solution of my problem
Sub AutoFilter_Range()
'AutoFilter is a member of the Range object
'Reference the entire range that the filters are applied to
'AutoFilter turns filters on/off when no parameters are specified.
Sheet1.Range("B3:G1000").AutoFilter
'Fully qualified reference starting at Workbook level
ThisWorkbook.Worksheets("AutoFilter Guide").Range("B3:G1000").AutoFilter
End Sub
And as we can read on How to use AutoFilters in Excel VBA Macros
AutoFilterMode and FilterMode must be set with False value
answered Jan 5 at 21:24
informerinformer
236
edited Jan 6 at 17:20
answered Jan 5 at 21:24
informerinformer
236
answered Jan 5 at 21:24
informerinformer
236
answered Jan 5 at 21:24
informerinformer
236
236
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54043409%2fautofilter-on-a-currentregion-resized%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
First things first, check
oXlsCurrentRegionwithMsgBox oXlsCurrentRegion.Address– chris neilsen
Jan 4 at 20:54
And maybe the first
AutoFilterModeshould beFalse– chris neilsen
Jan 4 at 20:56
Hi Chris, thanks for your help. I have to wait next Monday for checking address and changing for False. I make a feedback as soon as possible
– informer
Jan 5 at 7:12
Hi Chris, you're right about autofilterMode. Its value must be False! Thanks a lot for your help
– informer
Jan 6 at 17:15