Range Column and add Value to the specific range

-1

I have Excel file that I want to filter about one column and on another column add specific value

The Excel file

Sub Macro1()

'

' Macro1

'



'

    Range("Table1[type phone]").Select

    ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=1, Criteria1:= _

        "=*samsung*", Operator:=xlAnd

        Range("Table1[company]").Select



        'Here I want to  add the specific value "Samsung"'



    ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=1, Criteria1:= _

        "=*iphone*", Operator:=xlAnd

        Range("Table1[company]").Select



        'Here I want to  add the specific value "Apple"'



End Sub

Also how can I filter about one column Blanks cells and on another column add specific value "other"?

[ others Items[2]

What conditions can be written if, for example, the Samsung Devices table is missing?
Because if I run the code it crashes in the line it is looking for Samsung.

Without Samsung Devices

How can I do it?
Thank You for helping!

edited Jan 7 at 8:06

Pᴇʜ

24.1k63052

asked Jan 2 at 6:12

Eran Peer

335

2

If you want to apply the filter using VBA you might remove the existing filter, based on one column, and apply a new filter based on two columns.

– Variatus
Jan 2 at 7:07

How can I do it? Variatus

– Eran Peer
Jan 2 at 7:44

1

You might record a macro.

– Variatus
Jan 2 at 8:17

add a comment |

-1

I have Excel file that I want to filter about one column and on another column add specific value

The Excel file

Sub Macro1()

'

' Macro1

'



'

    Range("Table1[type phone]").Select

    ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=1, Criteria1:= _

        "=*samsung*", Operator:=xlAnd

        Range("Table1[company]").Select



        'Here I want to  add the specific value "Samsung"'



    ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=1, Criteria1:= _

        "=*iphone*", Operator:=xlAnd

        Range("Table1[company]").Select



        'Here I want to  add the specific value "Apple"'



End Sub

Also how can I filter about one column Blanks cells and on another column add specific value "other"?

[ others Items[2]

What conditions can be written if, for example, the Samsung Devices table is missing?
Because if I run the code it crashes in the line it is looking for Samsung.

Without Samsung Devices

How can I do it?
Thank You for helping!

edited Jan 7 at 8:06

Pᴇʜ

24.1k63052

asked Jan 2 at 6:12

Eran Peer

335

2

If you want to apply the filter using VBA you might remove the existing filter, based on one column, and apply a new filter based on two columns.

– Variatus
Jan 2 at 7:07

How can I do it? Variatus

– Eran Peer
Jan 2 at 7:44

1

You might record a macro.

– Variatus
Jan 2 at 8:17

add a comment |

-1

I have Excel file that I want to filter about one column and on another column add specific value

The Excel file

Sub Macro1()

'

' Macro1

'



'

    Range("Table1[type phone]").Select

    ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=1, Criteria1:= _

        "=*samsung*", Operator:=xlAnd

        Range("Table1[company]").Select



        'Here I want to  add the specific value "Samsung"'



    ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=1, Criteria1:= _

        "=*iphone*", Operator:=xlAnd

        Range("Table1[company]").Select



        'Here I want to  add the specific value "Apple"'



End Sub

Also how can I filter about one column Blanks cells and on another column add specific value "other"?

[ others Items[2]

What conditions can be written if, for example, the Samsung Devices table is missing?
Because if I run the code it crashes in the line it is looking for Samsung.

Without Samsung Devices

How can I do it?
Thank You for helping!

edited Jan 7 at 8:06

Pᴇʜ

24.1k63052

asked Jan 2 at 6:12

Eran Peer

335

I have Excel file that I want to filter about one column and on another column add specific value

The Excel file

Sub Macro1()

'

' Macro1

'



'

    Range("Table1[type phone]").Select

    ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=1, Criteria1:= _

        "=*samsung*", Operator:=xlAnd

        Range("Table1[company]").Select



        'Here I want to  add the specific value "Samsung"'



    ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=1, Criteria1:= _

        "=*iphone*", Operator:=xlAnd

        Range("Table1[company]").Select



        'Here I want to  add the specific value "Apple"'



End Sub

Also how can I filter about one column Blanks cells and on another column add specific value "other"?

[ others Items[2]

What conditions can be written if, for example, the Samsung Devices table is missing?
Because if I run the code it crashes in the line it is looking for Samsung.

Without Samsung Devices

How can I do it?
Thank You for helping!

excel vba excel-vba

edited Jan 7 at 8:06

Pᴇʜ

24.1k63052

asked Jan 2 at 6:12

Eran Peer

335

edited Jan 7 at 8:06

Pᴇʜ

24.1k63052

asked Jan 2 at 6:12

Eran Peer

335

edited Jan 7 at 8:06

Pᴇʜ

24.1k63052

edited Jan 7 at 8:06

Pᴇʜ

24.1k63052

edited Jan 7 at 8:06

Pᴇʜ

24.1k63052

asked Jan 2 at 6:12

Eran Peer

335

asked Jan 2 at 6:12

Eran Peer

335

asked Jan 2 at 6:12

Eran Peer

335

2

If you want to apply the filter using VBA you might remove the existing filter, based on one column, and apply a new filter based on two columns.

– Variatus
Jan 2 at 7:07

How can I do it? Variatus

– Eran Peer
Jan 2 at 7:44

1

You might record a macro.

– Variatus
Jan 2 at 8:17

add a comment |

2

If you want to apply the filter using VBA you might remove the existing filter, based on one column, and apply a new filter based on two columns.

– Variatus
Jan 2 at 7:07

How can I do it? Variatus

– Eran Peer
Jan 2 at 7:44

1

You might record a macro.

– Variatus
Jan 2 at 8:17

If you want to apply the filter using VBA you might remove the existing filter, based on one column, and apply a new filter based on two columns.

– Variatus
Jan 2 at 7:07

How can I do it? Variatus

– Eran Peer
Jan 2 at 7:44

You might record a macro.

– Variatus
Jan 2 at 8:17

add a comment |

1 Answer
1

active

oldest

votes

To change the value of only cells that are visible (aka those that are show in the filter) you can use SpecialCells(xlCellTypeVisible)

So for your example the code that would go in the first break would be

"Range("B2:B18").SpecialCells(xlCellTypeVisible).Value= "Samsung"

and the second break

"Range("B2:B18").SpecialCells(xlCellTypeVisible).Value= "Apple"

You need to change "18" to whatever the last row is. (Or you can define a variable called LastRow and then call that instead of hard-coding the number which means it will dynamically change based on how many rows there are.)

With LastRow

Sub Macro1()



 Dim LastRow As Long

  LastRow = ActiveSheet.Cells.Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row



    Range("Table1[type phone]").Select

    ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=1, Criteria1:= _

        "=*samsung*", Operator:=xlAnd

        Range("Table1[company]").Select



Range("B2:B" & LastRow).SpecialCells(xlCellTypeVisible).Value= "Samsung"



    ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=1, Criteria1:= _

        "=*iphone*", Operator:=xlAnd

        Range("Table1[company]").Select



Range("B2:B" & LastRow).SpecialCells(xlCellTypeVisible).Value= "Apple"

End Sub

We created a new variable called LastRow and defined it as long (A type of number). Then we defined LastRow according to a formula I use from this site:
https://www.thespreadsheetguru.com/blog/2014/7/7/5-different-ways-to-find-the-last-row-or-last-column-using-vba

Finally we replace Range("B2:B18") with Range("B2:B" & LastRow) which dynamically replaces the 18 with the number LastRow.

edited Jan 2 at 18:53

answered Jan 2 at 15:19

Emily Alden

407213

Thank you it is working but How can I get the last row with a variable?

– Eran Peer
Jan 2 at 18:45

@EranPeer I edited my post with the full code as well as a brief explanation. Feel free to ask further questions as needed.

– Emily Alden
Jan 2 at 18:54

Thank you very much but you can help me please to filter with blank cells to write "other" ?

– Eran Peer
Jan 2 at 19:56

@EranPeer I think you should be able to figure out how to do that by repeating the processes you already know. Try it first. If you cannot figure it out post the code you are using and I will help.

– Emily Alden
Jan 2 at 20:19

I got along but there is another problem unfortunately

– Eran Peer
Jan 2 at 22:46

|
show 3 more comments

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
});

}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54001990%2frange-column-and-add-value-to-the-specific-range%23new-answer', 'question_page');
}
);

Post as a guest

Name

Required, but never shown

1 Answer
1

active

oldest

votes

1 Answer
1

active

oldest

votes

To change the value of only cells that are visible (aka those that are show in the filter) you can use SpecialCells(xlCellTypeVisible)

So for your example the code that would go in the first break would be

"Range("B2:B18").SpecialCells(xlCellTypeVisible).Value= "Samsung"

and the second break

"Range("B2:B18").SpecialCells(xlCellTypeVisible).Value= "Apple"

With LastRow

Sub Macro1()



 Dim LastRow As Long

  LastRow = ActiveSheet.Cells.Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row



    Range("Table1[type phone]").Select

    ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=1, Criteria1:= _

        "=*samsung*", Operator:=xlAnd

        Range("Table1[company]").Select



Range("B2:B" & LastRow).SpecialCells(xlCellTypeVisible).Value= "Samsung"



    ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=1, Criteria1:= _

        "=*iphone*", Operator:=xlAnd

        Range("Table1[company]").Select



Range("B2:B" & LastRow).SpecialCells(xlCellTypeVisible).Value= "Apple"

End Sub

Finally we replace Range("B2:B18") with Range("B2:B" & LastRow) which dynamically replaces the 18 with the number LastRow.

edited Jan 2 at 18:53

answered Jan 2 at 15:19

Emily Alden

407213

Thank you it is working but How can I get the last row with a variable?

– Eran Peer
Jan 2 at 18:45

@EranPeer I edited my post with the full code as well as a brief explanation. Feel free to ask further questions as needed.

– Emily Alden
Jan 2 at 18:54

Thank you very much but you can help me please to filter with blank cells to write "other" ?

– Eran Peer
Jan 2 at 19:56

@EranPeer I think you should be able to figure out how to do that by repeating the processes you already know. Try it first. If you cannot figure it out post the code you are using and I will help.

– Emily Alden
Jan 2 at 20:19

I got along but there is another problem unfortunately

– Eran Peer
Jan 2 at 22:46

|
show 3 more comments

To change the value of only cells that are visible (aka those that are show in the filter) you can use SpecialCells(xlCellTypeVisible)

So for your example the code that would go in the first break would be

"Range("B2:B18").SpecialCells(xlCellTypeVisible).Value= "Samsung"

and the second break

"Range("B2:B18").SpecialCells(xlCellTypeVisible).Value= "Apple"

With LastRow

Sub Macro1()



 Dim LastRow As Long

  LastRow = ActiveSheet.Cells.Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row



    Range("Table1[type phone]").Select

    ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=1, Criteria1:= _

        "=*samsung*", Operator:=xlAnd

        Range("Table1[company]").Select



Range("B2:B" & LastRow).SpecialCells(xlCellTypeVisible).Value= "Samsung"



    ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=1, Criteria1:= _

        "=*iphone*", Operator:=xlAnd

        Range("Table1[company]").Select



Range("B2:B" & LastRow).SpecialCells(xlCellTypeVisible).Value= "Apple"

End Sub

Finally we replace Range("B2:B18") with Range("B2:B" & LastRow) which dynamically replaces the 18 with the number LastRow.

edited Jan 2 at 18:53

answered Jan 2 at 15:19

Emily Alden

407213

Thank you it is working but How can I get the last row with a variable?

– Eran Peer
Jan 2 at 18:45

@EranPeer I edited my post with the full code as well as a brief explanation. Feel free to ask further questions as needed.

– Emily Alden
Jan 2 at 18:54

Thank you very much but you can help me please to filter with blank cells to write "other" ?

– Eran Peer
Jan 2 at 19:56

@EranPeer I think you should be able to figure out how to do that by repeating the processes you already know. Try it first. If you cannot figure it out post the code you are using and I will help.

– Emily Alden
Jan 2 at 20:19

I got along but there is another problem unfortunately

– Eran Peer
Jan 2 at 22:46

|
show 3 more comments

To change the value of only cells that are visible (aka those that are show in the filter) you can use SpecialCells(xlCellTypeVisible)

So for your example the code that would go in the first break would be

"Range("B2:B18").SpecialCells(xlCellTypeVisible).Value= "Samsung"

and the second break

"Range("B2:B18").SpecialCells(xlCellTypeVisible).Value= "Apple"

With LastRow

Sub Macro1()



 Dim LastRow As Long

  LastRow = ActiveSheet.Cells.Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row



    Range("Table1[type phone]").Select

    ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=1, Criteria1:= _

        "=*samsung*", Operator:=xlAnd

        Range("Table1[company]").Select



Range("B2:B" & LastRow).SpecialCells(xlCellTypeVisible).Value= "Samsung"



    ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=1, Criteria1:= _

        "=*iphone*", Operator:=xlAnd

        Range("Table1[company]").Select



Range("B2:B" & LastRow).SpecialCells(xlCellTypeVisible).Value= "Apple"

End Sub

Finally we replace Range("B2:B18") with Range("B2:B" & LastRow) which dynamically replaces the 18 with the number LastRow.

edited Jan 2 at 18:53

answered Jan 2 at 15:19

Emily Alden

407213

To change the value of only cells that are visible (aka those that are show in the filter) you can use SpecialCells(xlCellTypeVisible)

So for your example the code that would go in the first break would be

"Range("B2:B18").SpecialCells(xlCellTypeVisible).Value= "Samsung"

and the second break

"Range("B2:B18").SpecialCells(xlCellTypeVisible).Value= "Apple"

With LastRow

Sub Macro1()



 Dim LastRow As Long

  LastRow = ActiveSheet.Cells.Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row



    Range("Table1[type phone]").Select

    ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=1, Criteria1:= _

        "=*samsung*", Operator:=xlAnd

        Range("Table1[company]").Select



Range("B2:B" & LastRow).SpecialCells(xlCellTypeVisible).Value= "Samsung"



    ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=1, Criteria1:= _

        "=*iphone*", Operator:=xlAnd

        Range("Table1[company]").Select



Range("B2:B" & LastRow).SpecialCells(xlCellTypeVisible).Value= "Apple"

End Sub

Finally we replace Range("B2:B18") with Range("B2:B" & LastRow) which dynamically replaces the 18 with the number LastRow.

edited Jan 2 at 18:53

answered Jan 2 at 15:19

Emily Alden

407213

edited Jan 2 at 18:53

answered Jan 2 at 15:19

Emily Alden

407213

answered Jan 2 at 15:19

Emily Alden

407213

answered Jan 2 at 15:19

Emily Alden

407213

Thank you it is working but How can I get the last row with a variable?

– Eran Peer
Jan 2 at 18:45

@EranPeer I edited my post with the full code as well as a brief explanation. Feel free to ask further questions as needed.

– Emily Alden
Jan 2 at 18:54

Thank you very much but you can help me please to filter with blank cells to write "other" ?

– Eran Peer
Jan 2 at 19:56

@EranPeer I think you should be able to figure out how to do that by repeating the processes you already know. Try it first. If you cannot figure it out post the code you are using and I will help.

– Emily Alden
Jan 2 at 20:19

I got along but there is another problem unfortunately

– Eran Peer
Jan 2 at 22:46

|
show 3 more comments

Thank you it is working but How can I get the last row with a variable?

– Eran Peer
Jan 2 at 18:45

@EranPeer I edited my post with the full code as well as a brief explanation. Feel free to ask further questions as needed.

– Emily Alden
Jan 2 at 18:54

Thank you very much but you can help me please to filter with blank cells to write "other" ?

– Eran Peer
Jan 2 at 19:56

@EranPeer I think you should be able to figure out how to do that by repeating the processes you already know. Try it first. If you cannot figure it out post the code you are using and I will help.

– Emily Alden
Jan 2 at 20:19

I got along but there is another problem unfortunately

– Eran Peer
Jan 2 at 22:46

Thank you it is working but How can I get the last row with a variable?

– Eran Peer
Jan 2 at 18:45

@EranPeer I edited my post with the full code as well as a brief explanation. Feel free to ask further questions as needed.

– Emily Alden
Jan 2 at 18:54

Thank you very much but you can help me please to filter with blank cells to write "other" ?

– Eran Peer
Jan 2 at 19:56

@EranPeer I think you should be able to figure out how to do that by repeating the processes you already know. Try it first. If you cannot figure it out post the code you are using and I will help.

– Emily Alden
Jan 2 at 20:19

I got along but there is another problem unfortunately

– Eran Peer
Jan 2 at 22:46

|
show 3 more comments

draft saved

draft discarded

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.

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Name

Required, but never shown

Name

Required, but never shown

This page is only for reference, If you need detailed information, please check here

Search This Blog

Ufyukyu