[Excel] خطأ في العثور على آخر خلية مستعملة في VBA



Answers

فيما يتعلق بالطريقة الصحيحة للعثور على آخر خلية مستعملة ، يتعين على المرء أولاً أن يقرر ما يعتبر مستخدمًا ، ثم يختار طريقة مناسبة . أتصور معنىين على الأقل:

  1. استخدام = غير فارغ ، أي ، وجود بيانات .

  2. تستخدم = "... قيد الاستخدام ، وهذا يعني القسم الذي يحتوي على البيانات أو التنسيق ." هذا هو المعيار المستخدم بواسطة Excel في وقت الحفظ . انظر أيضا this . عادة ما ينتج هذا المعيار نتائج غير متوقعة ، ولكن قد يتم استغلاله عن قصد (أقل شيوعًا ، بالتأكيد) ، على سبيل المثال ، لتسليط الضوء على مناطق معينة أو طباعتها ، والتي قد لا تحتوي في النهاية على بيانات. وبطبيعة الحال ، فمن المستحسن كمعيار للنطاق لاستخدامها عند حفظ مصنف.

تعتمد كيفية العثور على الخلية الأخيرة المستخدمة على ما تريد (المعيار الخاص بك) .

بالنسبة إلى المعيار 1 ، أقترح قراءة هذه الإجابة . لاحظ أنه يتم الاستشهاد بـ UsedRange أنه غير موثوق به. أعتقد أن هذا مضللة (أي "غير عادلة" إلى UsedRange ) ، كما لا يعني ببساطة UsedRange للإبلاغ عن آخر خلية تحتوي على بيانات. لذلك لا ينبغي أن تستخدم في هذه الحالة ، كما هو مبين في هذا الجواب. انظر أيضا هذا التعليق .

بالنسبة للمعيار 2 ، يعد UsedRange الخيار الأكثر موثوقية ، مقارنة بالخيارات الأخرى المصممة لهذا الاستخدام أيضًا. حتى أنه يجعل من غير الضروري حفظ مصنف للتأكد من أن يتم تحديث الخلية الأخيرة. سينتقل Ctrl + End إلى خلية غير صحيحة قبل الحفظ ("لم تتم إعادة تعيين آخر خلية حتى تحفظ ورقة العمل" ، من http://msdn.microsoft.com/en-us/library/aa139976%28v=office.10%29.aspx إنها إشارة قديمة ، ولكن في هذا الصدد صالحة).

هناك مشكلة أخرى:
لا يضع المعيار 2 حسابًا على التنسيق الشرطي. واحد قد يكون الخلايا المنسقة ، استنادا إلى الصيغ ، والتي لم يتم الكشف عنها من قبل UsedRange أو Ctrl + End . في الشكل ، الخلية الأخيرة هي B3 ، حيث تم تطبيق التنسيق بشكل صريح عليها. الخلايا B6: D7 يكون تنسيق مشتق من قاعدة تنسيق شرطي ، ولا يتم الكشف عن هذا حتى بواسطة UsedRange .

فيما يتعلق بسؤالك المحدد : ما السبب وراء هذا؟

تستخدم التعليمات البرمجية الخاصة بك الخلية الأولى في النطاق الخاص بك E4: E48 كـ trampoline ، للقفز لأسفل مع End(xlDown) .

سيحصل الإخراج "الخاطئ" على ما إذا لم تكن هناك خلايا غير فارغة في نطاقك بخلاف أولها. ثم ، أنت قفز في الظلام ، أي أسفل ورقة العمل (يجب أن تلاحظ الفرق بين السلسلة الفارغة والفارغة !).

لاحظ أن:

  1. إذا كان نطاقك يحتوي على خلايا غير متجاورة غير متجاورة ، فسيعطي أيضًا نتيجة خاطئة.

  2. إذا لم يكن هناك سوى خلية واحدة غير فارغة ، ولكنها ليست الخلية الأولى ، فسيظل رمزك يوفر لك النتيجة الصحيحة.

Question

عندما أريد العثور على آخر قيمة خلية مستعملة ، يمكنني استخدام:

Dim LastRow As Long

LastRow = Range("E4:E48").End(xlDown).Row

Debug.Print LastRow

أتلقى الإخراج الخطأ عندما أضع عنصرًا واحدًا في خلية. ولكن عندما أضع أكثر من قيمة في الخلية ، يكون الإخراج صحيحًا. ما هو السبب وراء هذا؟




أتساءل أن لا أحد قد ذكر ذلك ، ولكن أسهل طريقة للحصول على آخر خلية مستعملة هي:

Function GetLastCell(sh as Worksheet) As Range
    GetLastCell = sh.Cells(1,1).SpecialCells(xlLastCell)
End Function

يؤدي هذا بشكل أساسي إلى إرجاع الخلية نفسها التي تحصل عليها عن طريق Ctrl + End بعد تحديد الخلية A1 .

كلمة تحذير: يقوم برنامج Excel بتتبع الخلية الموجودة في أقصى الجانب الأيمن والتي تم استخدامها في ورقة العمل. لذلك إذا قمت مثلاً بإدخال شيء ما في B3 وشيء آخر في H8 ثم قمت لاحقًا بحذف محتويات H8 ، فسيظل ضغط Ctrl + End يأخذك إلى خلية H8 . الوظيفة المذكورة أعلاه سيكون لها نفس السلوك.




ومع ذلك ، يسعى هذا السؤال للعثور على الصف الأخير باستخدام VBA ، وأعتقد أنه سيكون من الجيد تضمين صيغة مصفوفة لورقة العمل حيث يتم زيارة هذا بشكل متكرر:

{=ADDRESS(MATCH(INDEX(D:D,MAX(IF(D:D<>"",ROW(D:D)-ROW(D1)+1)),1),D:D,0),COLUMN(D:D))}

تحتاج إلى إدخال الصيغة بدون الأقواس ومن ثم الضغط على Shift + Ctrl + Enter لتكوين صيغة صفيف.

سيعطيك هذا عنوان آخر خلية مستعملة في العمود D.




أود أن أضيف إلى الإجابة التي قدمها Siddarth Rout لتقول أنه يمكن تخطي استدعاء CountA من خلال البحث عن إرجاع كائن نطاق ، بدلاً من رقم صف ، ثم اختبار كائن النطاق الذي تم إرجاعه لمعرفة ما إذا كان لا شيء (ورقة عمل فارغة) .

أيضا ، أود أن يكون بلدي إصدار أي إجراء LastRow بإرجاع صفر لورقة عمل فارغة ، ثم أتمكن من معرفة أنها فارغة.




كنت أبحث عن طريقة لتقليد CTRL + Shift + End ، لذا فإن حل dotNET أمر رائع ، إلا أنه مع Excel 2010 ، أحتاج إلى إضافة set إذا كنت أريد تجنب خطأ:

Function GetLastCell(sh As Worksheet) As Range
  Set GetLastCell = sh.Cells(1, 1).SpecialCells(xlLastCell)
End Function

وكيفية التحقق من ذلك بنفسك:

Sub test()
  Dim ws As Worksheet, r As Range
  Set ws = ActiveWorkbook.Sheets("Sheet1")
  Set r = GetLastCell(ws)
  MsgBox r.Column & "-" & r.Row
End Sub





Links