Astuces Excel
  1. Astuces Excel
  3. Renvoie une gamme de A1 à la cellule dernière dernière utilisée
Intereting Posts
Trouver et replace et boucler une table Comment get les colonnes d'une feuille Excel qui ont une string spécifique et les écrire dans un nouveau file excel Comment écrire la valeur d'invite SAS dans Excel pour exécuter un process stocké? Boîte de dialog d'erreur affichée lors de l'ouverture d'un file Excel généré avec EPPlus Inclure PDF, XLS, DOC et d'autres formats dans le rapport SQL Ecran ExcelUpdation Écran faux et scintillant Essayer de se connecter au server sql 2012 à partir d'Excel en utilisant la connection ADODB Tricky: Déplacement de toutes datatables de la colonne C vers la dernière colonne occupée, suppression de toutes les cellules vides entre et transfert toutes datatables vers la droite Fonction de préréglage de la moyenne logarithmique dans excel – utilisation des plages comme valeurs d'input Dans VBA Excel via ADO SQL, Comment puis-je me référer à un logging "une seule fois" et le lier à un autre en évitant le même logging à callbacker? Calcul de la différence de time entre : mm Comment append du text à une cellule de formule? Comparez 2 cellules et searchz des valeurs manquantes dans Excel Comment changer les sauvegardes à download Télécharger le file C # Calcul de l'erreur standard Vba

Renvoie une gamme de A1 à la cellule dernière dernière utilisée

Je voudrais sélectionner toutes les lignes et les colonnes dans une feuille de calcul. La macro doit être dynamic, car le nombre de colonnes et de lignes a tendance à varier chaque fois que la macro serait appelée. Il doit également être en mesure de comptabiliser les lignes et les colonnes vierges.

Ce sous-programme accomplit une partie du process: 

Sub FindLastCell() Cells.Find(What:="*", After:=[A1], SearchDirection:=xlPrevious).Select End Sub

Il trouve et sélectionne la dernière cellule dans une feuille de calcul. Maintenant que j'ai trouvé la dernière cellule dans la feuille de calcul, comment puis-je sélectionner la cellule A1 vers LastCell en tant que gamme?

Vous devez faire ces mods au code

  1. La feuille peut être vierge, donc vous ne devriez jamais utiliser Select with Find car cela donnera une erreur si le Find n'a rien renvoyé. Au lieu de tester que l'object de scope Is Not Nothing
  2. Find peut searchr par rangée et par colonne. Vous devez déterminer la dernière ligne et la colonne pour déterminer la dernière cellule utilisée
  3. Une fois que vous avez déterminé la Range utilisation de la dernière cellule Range définissez une plage allant de la première cellule (A1) à votre cellule déterminée avec les deux plages de Find

Voir le code ci-dessous

Si le Find obtient une valeur alors il fait une gamme rng3 de A1 à la dernière cellule utilisée identifiée par les deux Find s.

entrez la description de l'image ici

 Sub GetRange() Dim rng1 As Range Dim rng2 As Range Dim rng3 As Range Set rng1 = Cells.Find("*", [a1], xlFormulas, , xlByRows, xlPrevious) Set rng2 = Cells.Find("*", [a1], xlFormulas, , xlByColumns, xlPrevious) If Not rng1 Is Nothing Then Set rng3 = Range([a1], Cells(rng1.Row, rng2.Column)) MsgBox "Range is " & rng3.Address(0, 0) 'if you need to actual select the range (which is rare in VBA) Application.Goto rng3 Else MsgBox "sheet is blank", vbCritical End If End Sub Sub GetRange () Sub GetRange() Dim rng1 As Range Dim rng2 As Range Dim rng3 As Range Set rng1 = Cells.Find("*", [a1], xlFormulas, , xlByRows, xlPrevious) Set rng2 = Cells.Find("*", [a1], xlFormulas, , xlByColumns, xlPrevious) If Not rng1 Is Nothing Then Set rng3 = Range([a1], Cells(rng1.Row, rng2.Column)) MsgBox "Range is " & rng3.Address(0, 0) 'if you need to actual select the range (which is rare in VBA) Application.Goto rng3 Else MsgBox "sheet is blank", vbCritical End If End Sub Dim rng1 As Range Sub GetRange() Dim rng1 As Range Dim rng2 As Range Dim rng3 As Range Set rng1 = Cells.Find("*", [a1], xlFormulas, , xlByRows, xlPrevious) Set rng2 = Cells.Find("*", [a1], xlFormulas, , xlByColumns, xlPrevious) If Not rng1 Is Nothing Then Set rng3 = Range([a1], Cells(rng1.Row, rng2.Column)) MsgBox "Range is " & rng3.Address(0, 0) 'if you need to actual select the range (which is rare in VBA) Application.Goto rng3 Else MsgBox "sheet is blank", vbCritical End If End Sub Dim rng2 As Range Sub GetRange() Dim rng1 As Range Dim rng2 As Range Dim rng3 As Range Set rng1 = Cells.Find("*", [a1], xlFormulas, , xlByRows, xlPrevious) Set rng2 = Cells.Find("*", [a1], xlFormulas, , xlByColumns, xlPrevious) If Not rng1 Is Nothing Then Set rng3 = Range([a1], Cells(rng1.Row, rng2.Column)) MsgBox "Range is " & rng3.Address(0, 0) 'if you need to actual select the range (which is rare in VBA) Application.Goto rng3 Else MsgBox "sheet is blank", vbCritical End If End Sub Dim rng3 As Range Sub GetRange() Dim rng1 As Range Dim rng2 As Range Dim rng3 As Range Set rng1 = Cells.Find("*", [a1], xlFormulas, , xlByRows, xlPrevious) Set rng2 = Cells.Find("*", [a1], xlFormulas, , xlByColumns, xlPrevious) If Not rng1 Is Nothing Then Set rng3 = Range([a1], Cells(rng1.Row, rng2.Column)) MsgBox "Range is " & rng3.Address(0, 0) 'if you need to actual select the range (which is rare in VBA) Application.Goto rng3 Else MsgBox "sheet is blank", vbCritical End If End Sub Définir rng1 = Cells.Find ("*", [a1], xlFormulas,, xlByRows, xlPrevious) Sub GetRange() Dim rng1 As Range Dim rng2 As Range Dim rng3 As Range Set rng1 = Cells.Find("*", [a1], xlFormulas, , xlByRows, xlPrevious) Set rng2 = Cells.Find("*", [a1], xlFormulas, , xlByColumns, xlPrevious) If Not rng1 Is Nothing Then Set rng3 = Range([a1], Cells(rng1.Row, rng2.Column)) MsgBox "Range is " & rng3.Address(0, 0) 'if you need to actual select the range (which is rare in VBA) Application.Goto rng3 Else MsgBox "sheet is blank", vbCritical End If End Sub Définir rng2 = Cells.Find ("*", [a1], xlFormulas,, xlByColumns, xlPrevious) Sub GetRange() Dim rng1 As Range Dim rng2 As Range Dim rng3 As Range Set rng1 = Cells.Find("*", [a1], xlFormulas, , xlByRows, xlPrevious) Set rng2 = Cells.Find("*", [a1], xlFormulas, , xlByColumns, xlPrevious) If Not rng1 Is Nothing Then Set rng3 = Range([a1], Cells(rng1.Row, rng2.Column)) MsgBox "Range is " & rng3.Address(0, 0) 'if you need to actual select the range (which is rare in VBA) Application.Goto rng3 Else MsgBox "sheet is blank", vbCritical End If End Sub Si non rng1 n'est rien alors Sub GetRange() Dim rng1 As Range Dim rng2 As Range Dim rng3 As Range Set rng1 = Cells.Find("*", [a1], xlFormulas, , xlByRows, xlPrevious) Set rng2 = Cells.Find("*", [a1], xlFormulas, , xlByColumns, xlPrevious) If Not rng1 Is Nothing Then Set rng3 = Range([a1], Cells(rng1.Row, rng2.Column)) MsgBox "Range is " & rng3.Address(0, 0) 'if you need to actual select the range (which is rare in VBA) Application.Goto rng3 Else MsgBox "sheet is blank", vbCritical End If End Sub Définir rng3 = Gamme ([a1], Cellules (rng1.Row, rng2.Column)) Sub GetRange() Dim rng1 As Range Dim rng2 As Range Dim rng3 As Range Set rng1 = Cells.Find("*", [a1], xlFormulas, , xlByRows, xlPrevious) Set rng2 = Cells.Find("*", [a1], xlFormulas, , xlByColumns, xlPrevious) If Not rng1 Is Nothing Then Set rng3 = Range([a1], Cells(rng1.Row, rng2.Column)) MsgBox "Range is " & rng3.Address(0, 0) 'if you need to actual select the range (which is rare in VBA) Application.Goto rng3 Else MsgBox "sheet is blank", vbCritical End If End Sub MsgBox "Range est" & rng3.Address (0, 0) Sub GetRange() Dim rng1 As Range Dim rng2 As Range Dim rng3 As Range Set rng1 = Cells.Find("*", [a1], xlFormulas, , xlByRows, xlPrevious) Set rng2 = Cells.Find("*", [a1], xlFormulas, , xlByColumns, xlPrevious) If Not rng1 Is Nothing Then Set rng3 = Range([a1], Cells(rng1.Row, rng2.Column)) MsgBox "Range is " & rng3.Address(0, 0) 'if you need to actual select the range (which is rare in VBA) Application.Goto rng3 Else MsgBox "sheet is blank", vbCritical End If End Sub 'si vous devez réellement sélectionner la gamme (ce qui est rare dans VBA) Sub GetRange() Dim rng1 As Range Dim rng2 As Range Dim rng3 As Range Set rng1 = Cells.Find("*", [a1], xlFormulas, , xlByRows, xlPrevious) Set rng2 = Cells.Find("*", [a1], xlFormulas, , xlByColumns, xlPrevious) If Not rng1 Is Nothing Then Set rng3 = Range([a1], Cells(rng1.Row, rng2.Column)) MsgBox "Range is " & rng3.Address(0, 0) 'if you need to actual select the range (which is rare in VBA) Application.Goto rng3 Else MsgBox "sheet is blank", vbCritical End If End Sub Application.Goto rng3 Sub GetRange() Dim rng1 As Range Dim rng2 As Range Dim rng3 As Range Set rng1 = Cells.Find("*", [a1], xlFormulas, , xlByRows, xlPrevious) Set rng2 = Cells.Find("*", [a1], xlFormulas, , xlByColumns, xlPrevious) If Not rng1 Is Nothing Then Set rng3 = Range([a1], Cells(rng1.Row, rng2.Column)) MsgBox "Range is " & rng3.Address(0, 0) 'if you need to actual select the range (which is rare in VBA) Application.Goto rng3 Else MsgBox "sheet is blank", vbCritical End If End Sub MsgBox "est vide", vbCritical Sub GetRange() Dim rng1 As Range Dim rng2 As Range Dim rng3 As Range Set rng1 = Cells.Find("*", [a1], xlFormulas, , xlByRows, xlPrevious) Set rng2 = Cells.Find("*", [a1], xlFormulas, , xlByColumns, xlPrevious) If Not rng1 Is Nothing Then Set rng3 = Range([a1], Cells(rng1.Row, rng2.Column)) MsgBox "Range is " & rng3.Address(0, 0) 'if you need to actual select the range (which is rare in VBA) Application.Goto rng3 Else MsgBox "sheet is blank", vbCritical End If End Sub

Choisir A1 vers la dernière cellule utilisée (y compris les blancs intermédiaires) dans la colonne A est aussi simple que ceci: 

 Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row).Select

Pour sélectionner A1 vers la dernière cellule utilisée dans la feuille entière (peu importe si elle est utilisée dans la colonne A ou non): 

 Range("A1:A" & ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row).Select

J'ai compris comment modifier le code pour sélectionner une plage commençant à une cellule spécifiée et se terminant à la dernière cellule. À la ligne 8, modifiez a1 sur la cellule sur laquelle vous souhaitez démarrer. Dans mon cas, j'ai choisi j28. 

  Set rng3 = Range([j28], Cells(rng1.Row, rng2.Column))

code complet: 

  Sub GetRange() Dim rng1 As Range Dim rng2 As Range Dim rng3 As Range Set rng1 = Cells.Find("*", [a1], , , xlByRows, xlPrevious) Set rng2 = Cells.Find("*", [a1], , , xlByColumns, xlPrevious) If Not rng1 Is Nothing Then Set rng3 = Range([j28], Cells(rng1.Row, rng2.Column)) MsgBox "Range is " & rng3.Address(0, 0) 'if you need to actual select the range (which is rare in VBA) Application.Goto rng3 Else MsgBox "sheet is blank", vbCritical End If End Sub Sub GetRange ()  Sub GetRange() Dim rng1 As Range Dim rng2 As Range Dim rng3 As Range Set rng1 = Cells.Find("*", [a1], , , xlByRows, xlPrevious) Set rng2 = Cells.Find("*", [a1], , , xlByColumns, xlPrevious) If Not rng1 Is Nothing Then Set rng3 = Range([j28], Cells(rng1.Row, rng2.Column)) MsgBox "Range is " & rng3.Address(0, 0) 'if you need to actual select the range (which is rare in VBA) Application.Goto rng3 Else MsgBox "sheet is blank", vbCritical End If End Sub Dim rng1 As Range  Sub GetRange() Dim rng1 As Range Dim rng2 As Range Dim rng3 As Range Set rng1 = Cells.Find("*", [a1], , , xlByRows, xlPrevious) Set rng2 = Cells.Find("*", [a1], , , xlByColumns, xlPrevious) If Not rng1 Is Nothing Then Set rng3 = Range([j28], Cells(rng1.Row, rng2.Column)) MsgBox "Range is " & rng3.Address(0, 0) 'if you need to actual select the range (which is rare in VBA) Application.Goto rng3 Else MsgBox "sheet is blank", vbCritical End If End Sub Dim rng2 As Range  Sub GetRange() Dim rng1 As Range Dim rng2 As Range Dim rng3 As Range Set rng1 = Cells.Find("*", [a1], , , xlByRows, xlPrevious) Set rng2 = Cells.Find("*", [a1], , , xlByColumns, xlPrevious) If Not rng1 Is Nothing Then Set rng3 = Range([j28], Cells(rng1.Row, rng2.Column)) MsgBox "Range is " & rng3.Address(0, 0) 'if you need to actual select the range (which is rare in VBA) Application.Goto rng3 Else MsgBox "sheet is blank", vbCritical End If End Sub Dim rng3 As Range  Sub GetRange() Dim rng1 As Range Dim rng2 As Range Dim rng3 As Range Set rng1 = Cells.Find("*", [a1], , , xlByRows, xlPrevious) Set rng2 = Cells.Find("*", [a1], , , xlByColumns, xlPrevious) If Not rng1 Is Nothing Then Set rng3 = Range([j28], Cells(rng1.Row, rng2.Column)) MsgBox "Range is " & rng3.Address(0, 0) 'if you need to actual select the range (which is rare in VBA) Application.Goto rng3 Else MsgBox "sheet is blank", vbCritical End If End Sub Définir rng1 = Cells.Find ("*", [a1],,, xlByRows, xlPrevious)  Sub GetRange() Dim rng1 As Range Dim rng2 As Range Dim rng3 As Range Set rng1 = Cells.Find("*", [a1], , , xlByRows, xlPrevious) Set rng2 = Cells.Find("*", [a1], , , xlByColumns, xlPrevious) If Not rng1 Is Nothing Then Set rng3 = Range([j28], Cells(rng1.Row, rng2.Column)) MsgBox "Range is " & rng3.Address(0, 0) 'if you need to actual select the range (which is rare in VBA) Application.Goto rng3 Else MsgBox "sheet is blank", vbCritical End If End Sub Définir rng2 = Cells.Find ("*", [a1],,, xlByColumns, xlPrevious)  Sub GetRange() Dim rng1 As Range Dim rng2 As Range Dim rng3 As Range Set rng1 = Cells.Find("*", [a1], , , xlByRows, xlPrevious) Set rng2 = Cells.Find("*", [a1], , , xlByColumns, xlPrevious) If Not rng1 Is Nothing Then Set rng3 = Range([j28], Cells(rng1.Row, rng2.Column)) MsgBox "Range is " & rng3.Address(0, 0) 'if you need to actual select the range (which is rare in VBA) Application.Goto rng3 Else MsgBox "sheet is blank", vbCritical End If End Sub Si non rng1 n'est rien alors  Sub GetRange() Dim rng1 As Range Dim rng2 As Range Dim rng3 As Range Set rng1 = Cells.Find("*", [a1], , , xlByRows, xlPrevious) Set rng2 = Cells.Find("*", [a1], , , xlByColumns, xlPrevious) If Not rng1 Is Nothing Then Set rng3 = Range([j28], Cells(rng1.Row, rng2.Column)) MsgBox "Range is " & rng3.Address(0, 0) 'if you need to actual select the range (which is rare in VBA) Application.Goto rng3 Else MsgBox "sheet is blank", vbCritical End If End Sub Définir rng3 = Plage ([j28], Cellules (rng1.Row, rng2.Column))  Sub GetRange() Dim rng1 As Range Dim rng2 As Range Dim rng3 As Range Set rng1 = Cells.Find("*", [a1], , , xlByRows, xlPrevious) Set rng2 = Cells.Find("*", [a1], , , xlByColumns, xlPrevious) If Not rng1 Is Nothing Then Set rng3 = Range([j28], Cells(rng1.Row, rng2.Column)) MsgBox "Range is " & rng3.Address(0, 0) 'if you need to actual select the range (which is rare in VBA) Application.Goto rng3 Else MsgBox "sheet is blank", vbCritical End If End Sub MsgBox "Range est" & rng3.Address (0, 0)  Sub GetRange() Dim rng1 As Range Dim rng2 As Range Dim rng3 As Range Set rng1 = Cells.Find("*", [a1], , , xlByRows, xlPrevious) Set rng2 = Cells.Find("*", [a1], , , xlByColumns, xlPrevious) If Not rng1 Is Nothing Then Set rng3 = Range([j28], Cells(rng1.Row, rng2.Column)) MsgBox "Range is " & rng3.Address(0, 0) 'if you need to actual select the range (which is rare in VBA) Application.Goto rng3 Else MsgBox "sheet is blank", vbCritical End If End Sub 'si vous devez réellement sélectionner la gamme (ce qui est rare dans VBA)  Sub GetRange() Dim rng1 As Range Dim rng2 As Range Dim rng3 As Range Set rng1 = Cells.Find("*", [a1], , , xlByRows, xlPrevious) Set rng2 = Cells.Find("*", [a1], , , xlByColumns, xlPrevious) If Not rng1 Is Nothing Then Set rng3 = Range([j28], Cells(rng1.Row, rng2.Column)) MsgBox "Range is " & rng3.Address(0, 0) 'if you need to actual select the range (which is rare in VBA) Application.Goto rng3 Else MsgBox "sheet is blank", vbCritical End If End Sub Application.Goto rng3  Sub GetRange() Dim rng1 As Range Dim rng2 As Range Dim rng3 As Range Set rng1 = Cells.Find("*", [a1], , , xlByRows, xlPrevious) Set rng2 = Cells.Find("*", [a1], , , xlByColumns, xlPrevious) If Not rng1 Is Nothing Then Set rng3 = Range([j28], Cells(rng1.Row, rng2.Column)) MsgBox "Range is " & rng3.Address(0, 0) 'if you need to actual select the range (which is rare in VBA) Application.Goto rng3 Else MsgBox "sheet is blank", vbCritical End If End Sub MsgBox "est vide", vbCritical  Sub GetRange() Dim rng1 As Range Dim rng2 As Range Dim rng3 As Range Set rng1 = Cells.Find("*", [a1], , , xlByRows, xlPrevious) Set rng2 = Cells.Find("*", [a1], , , xlByColumns, xlPrevious) If Not rng1 Is Nothing Then Set rng3 = Range([j28], Cells(rng1.Row, rng2.Column)) MsgBox "Range is " & rng3.Address(0, 0) 'if you need to actual select the range (which is rare in VBA) Application.Goto rng3 Else MsgBox "sheet is blank", vbCritical End If End Sub