Macro VBA do Excel para classificar dados. Não gosto de como o gravador de macro cria código extra para classificação. A classificação no Excel VBA deve ser simples. Uma linha de código, com a coluna a classificar, de que forma (crescente ou decrescente) e se há um cabeçalho.
Assistir vídeo
- O gravador de macro do Excel não faz um bom trabalho com a classificação de gravações.
- Desde que seus dados possam ser selecionados usando Ctrl + * (conhecido como a região atual)
- Desde que você não esteja classificando por cor ou ícone ou mais de três níveis
- Use o método antigo Range (). CurrentRegion.Sort no Excel
Transcrição de vídeo
Aprenda Excel com MrExcel Podcast, Episódio 2093: Sorting With VBA
Ei, bem-vindo de volta ao netcast, sou Bill Jelen. A pergunta de hoje de James em Huntsville. James, usei o gravador de macro para registrar a ação de classificação de dados. Então, digamos que James precisasse classificar esses dados por setor e clientes. Então, você chega aqui à guia Exibir, Macros, Gravar nova macro, SortMyReports, Tecla de atalho Ctrl - digitarei Shift + S lá e clicarei em OK. Tudo bem, então a partir daqui fazemos: Dados, Classificar e queremos dizer que queremos Classificar por Setor e, em seguida, Adicionar um Nível e Classificar por Cliente e clicar em OK. Abaixo, no canto esquerdo inferior, clicamos em Parar gravação. Tudo bem, então aí está. Parece ter funcionado, certo?
Mas aqui está o problema: amanhã você terá mais ou menos dados ou, você sabe, o que for. E essa macro gravada é simplesmente horrível. Vamos dar uma olhada, vou fazer Alt + F8 e dar uma olhada no SortMyReport, vou editar isso. Tudo bem, isso é tudo que eles gravaram para o SortFields.Clear, e então eles configuraram uma nova classificação com o SortFields.Add e eles estão codificados, e há 568 linhas e todas as outras coisas.
Agora, voltando ao dia, foi muito, muito fácil. Tudo bem, e no Excel 2007 eles adicionaram Classificar por ícone, Classificar por cor, Classificar por cor da fonte, a capacidade de ter 15 classificações de nível e tudo ficou muito, muito insano. Então, eu não uso mais a Macro gravada. Eu apenas vou à velha escola.
Agora, vou voltar para o Excel. Aqui estão as regras para que essa classificação à moda antiga funcione, certo. Título acima de cada coluna: esse título deve estar em uma linha, não em duas linhas. Se você tem títulos lá em cima, tudo bem ter títulos lá. Você precisa de uma linha completamente em branco entre seus títulos e o primeiro título. Se você tiver anotações no lado direito: sua esposa liga com uma lista de compras: “Ei, querida, pare no caminho para casa. Pegue leite, ovos e vodka. ” Deve haver uma coluna completamente em branco entre seus dados e isso. E se houver notas padronizadas na parte inferior, certifique-se de que haja uma linha completamente em branco entre o último bit de dados e essas notas.
Todo o meu objetivo é que devemos ser capazes de chegar a qualquer célula: a célula do canto superior esquerdo esses dados e pressione Ctrl + * e irá selecionar os dados a serem classificados. Agora, vou pressionar Ctrl +. que nos leva a este canto e depois Ctrl +. nos levará ao canto inferior direito, Ctrl +. nos leva ao canto esquerdo inferior. Tudo bem, então se Ctrl + * irá selecionar seus dados corretamente, então está tudo ótimo. Se você colocar sua lista de compras na coluna H e vermos que subimos aqui e Ctrl + *, bem agora, estamos classificando a lista de compras como parte da coisa e sua lista de compras ficará bagunçada. Ou vamos desfazer: se esta linha não estiver aqui, agora fazemos Ctrl + *, veja que estamos- agora eles não terão mais noção porque não têm mais títulos, certo?
Portanto, se você for usar meu código, certifique-se de que todas essas regras sejam verdadeiras: sem classificar minha cor, sem classificar meu ícone, 3 ou menos níveis de classificação. Desfazer, certo. Então aqui está o que sabemos: sabemos que todos os dias nossos dados começarão em A5. Se não soubermos quantas linhas ou quantas - bem, até quantas colunas podemos ter. Não consigo imaginar uma situação em que as colunas sejam trocadas, mas certamente o número de linhas vai mudar. Então Alt + F11, vamos simplesmente começar a partir da célula do canto superior esquerdo. Então, Range, no meu caso, é “A5” .CurrentRegion. A região atual é esta propriedade de construção incrível que diz que vamos pressionar Ctrl + Shift + * e tudo o que está incluído lá é o que será classificado. E nós fazemos .Sort. .Sort, certo.
Agora, aqui está a coisa. Se você quiser fazer uma classificação em um nível, é fácil: Chave1: =. : = e nós apenas dizemos que vai ser Range - Oh, eu esqueci o que é. Foi Setor, onde está Setor? O setor está na coluna C. Portanto, C5 no meu caso, Intervalo (“C5”) e, em seguida, Ordem1: = xlAscendente Pressionei a tecla de seta para baixo ali e depois Tab. Tudo bem agora, eu poderia continuar indo para a direita, mas não vou fazer isso. Vou para uma nova linha então espaço, sublinhado para ir para uma nova linha, continua essa linha de código, certo? E se eu tiver uma classificação de segundo nível: Key2: = e, neste caso, eu quero classificar por cliente que está na coluna D, então D5. E então, Order2: xlAscending. Bonito.
Não tenho uma classificação de terceiro nível, mas se você tivesse, seria Key3 e, em seguida, Order3. E então este próximo, o que você tem que fazer é Header, certo? Então, Header: = xl Acho que é aí que você está tendo muitos problemas. E então vamos dizer xl Sim, definitivamente como um cabeçalho. Mesmo nos velhos tempos, o gravador de macro usava xlGuess. Odeio que o Excel adivinhe.
É isso aí. Uma linha de código, isso é tudo que você precisa fazer e funcionará com mais linhas, menos linhas. É uma coisa linda, linda. Tudo bem, então vamos voltar aqui para o Excel. Ctrl + Shift + S ainda é o que está atribuído. Agora- Se você acabou de mudar para o VBA e você mesmo digitar, você pode ir para Alt + F8, encontrar o nome de sua macro, clicar em Opções e digitar Ctrl + Shift + S lá ou podemos até atribuí-lo a um Tecla de atalho aqui na barra de ferramentas de acesso rápido. Clique com o botão direito, Personalizar Barra de Ferramentas de Acesso Rápido onde eu escolho uma de nossas Macros Eu tenho uma macro chamada SortMyReport, clicarei em Adicionar - odeio o pequeno fluxograma aí. Vamos modificar isso e eu adoraria que houvesse algum tipo de situação de A a Z, mas é claro que não. Talvez aquela flecha quem sabe, quem sabe, escolha qualquer coisa.A bola-8 mágica, não sei. Vou escolher este rapaz aqui, clique em OK, clique em OK. Tudo bem, agora nossos dados são classificados por data, eu escolho - e não importa o que eu escolher. Sempre vai voltar e classificar de A5, eu clico no rapaz e meus dados agora são Classificados por Setor, dentro de setor, por Cliente. Funciona muito bem, certo?
Então, se você é um fã do Gravador de Macro, meus melhores votos de coração. Mas o código do Macro Recorder atualmente para classificação em VBA é muito mais simples; para voltar, simplesmente use essencialmente esta, uma linha de código.
Bem, normalmente é aqui que tento fazer com que você compre este livro, mas hoje acho que você deveria dar uma olhada neste livro: Excel 2016 VBA e Macros de Tracy e eu. Uau! Veja isso. Não sabia que existe uma versão em outro idioma. Vamos ajudá-lo a aprimorar completamente a curva de aprendizado de macro, desde a gravação de sua primeira macro até o código de que você precisa.
Bem, um resumo simples por hoje: o Gravador de macros do Excel não faz um bom trabalho com gravação e classificação: desde que seus dados possam ser selecionados usando Ctrl + * que é conhecido como a região atual, desde que você não esteja classificando como cor ou ícone ou mais de três níveis, basta usar o método antigo Range (). CurrentRegion.Sort no VBA para classificar.
Quero agradecer ao James por enviar essa pergunta. Quero agradecer a sua visita. Nos vemos na próxima vez para outro netcast de.
⇬ Fazer download do arquivo
Baixe o arquivo de amostra aqui: Podcast2093.xlsm
