در برنامه نویسی وی بی ای شما میتوانید اعمال مختلفی از قبیل کپی، کات، اصلاح، انتقال، انتخاب و … را بر روی سلول ها و محدوده ها انجام دهید. بنابراین برای بررسی تمامی این موارد در برنامه نویسی VBA این مطلب را به بخش های مختلفی تقسیم میکنیم و در هر بخش به یکی از این فعالیت‌ها میپردازیم.

انتخاب یک سلول/محدوده در اکسل با استفاده از VBA

البته همیشه برای کار با سلول ها و محدوده ها نیاز به انتخاب کردن انها نیست. در مورد دلایل این موضوع بعدا صحبت خواهد شد ولی از آنجایی که انتخاب کردن سلول ها و محدوده ها یکی از اعمال پرکاربرد در اکسل و VBA است، در زیر نحوه انجام کار توضیح داده شده است.

انتخاب یک سلول با استفاده از VBA

برای انتخاب یک سلول(مثلا A1) در شیت فعال میتوانید از کد ساده زیر استفاده کنید:

sub selectcell()
range("A1").select
end sub

کد بالا از قسمت های اجباری sub و end sub و یک سطر کد برای انتخاب سلول A1 تشکیل شده است. (“Range(“A1 ادرس سلولی را که میخواهیم به آن اشاره کنیم مشخص میکند.

Select یک متد برای محدوده A1 است و با استفاده از آن محدوده خواسته شده انتخاب میشود. آدرس سلول می بایست در بین علامت دابل کوتیشن قرار گیرد.

همانطور که در قسمت قبل گفته شد از آنجایی که محدوده خواسته شده در شیتِ فعال است ، نیازی به مشخص کردن شیت در کدنویسی نیست ولی اگر محدوده خواسته شده در یک شیت دیگر باشد می بایست به صورت زیر کدنویسی کرد:

Sub SelectCell()
Worksheets("Sheet2").Activate
Range("A1").Select
End Sub

به همین شکل میتوانید ابتدا یک ورک بوک را مشخص کنید سپس یکی از ورک شیت های آن را فعال کنید و سپس سلول مورد نظر را انتخاب کنید:

Sub SelectCell()
Workbooks("Book2.xlsx").Worksheets("Sheet2").Activate
Range("A1").Select
End Sub

توجه داشته باشید که وقتی به یک ورک بوک اشاره میکنید می بایست نام کامل ورک بوک را وارد کنید و سپس پسوند فایل به درستی وارد شود( در این مثال .xlsx)

یک روش دیگر برای انجام این کار استفاده از کد زیر است:

Sub SelectRange()
Range("A1", "D20").Select
End Sub

روش دوم، ابتدا سلول بالا سمت چپ(A1) و سپس سلول پایین سمت راست (D20) را می گیرد و سپس محدوده بین این دو را انتخاب میکند. این روش زمانی که میخواهیم با محدوده های متغیر کار کنیم بسیار مفید خواهد بود.

اگر میخواهید انتخاب محدوده در یک ورک بوک یا ورک شیتِ متفاوت رخ دهد، می بایست نام کامل آنها را در کدنویسی VBA مشخص کنید. برای مثال در کد زیر محدوده مورد نظر در ورک شیت Sheet2 و ورک بوک book2 قرار دارد.

Sub SelectRange()
Workbooks("Book2.xlsx").Worksheets("Sheet1").Activate
Range("A1:D20").Select
End Sub

حال اگر ندانیم چند ردیف می بایست انتخاب شود چه باید کرد؟ یا اگر بخواهیم فقط سلول هایی انتخاب شود که دارای مقدار هستند. در این موارد می بایست از روش گفته شده در زیر استفاد کنید.

انتخاب یک محدوده متغیر در اکسل با استفاده از VBA

روش های مختلفی برای انتخاب یک محدوده سلولی وجود دارد. روشی که برای انجام این کار استفاده میکنید بستگی به ساختار داده ها شما دارد.

انتخاب با استفاده از مشخصه CurrentRange

در مواردی که دقیقا نمیدانیم چه تعداد ردیف یا ستون دارای داده هستند میتوانید از مشخصه CurrentRange استفاده کنید. این مشخصه تمام سلول های دارای مقدار پست سر هم را در یک محدوده پوشش میدهد . برای مثال در کد زیر محدوده current region مربوط به سلول A1 انتخاب میشود. به این معنی که محدوده شروع شده از سلول a1 تا آخرین سلول دارای مقدار انتخاب میشود.

Sub SelectCurrentRegion()
Range("A1").CurrentRegion.Select
End Sub

روش بالا زمانی مفید است که سلول خالی در بین محدوده مورد نظر وجود نداشته باشد. در مواردی که در بین سلول ها سلول خالی وجود دارد، این روش جواب نخواهد داد زیرا همانطور که گفتیم مشخصه currenregion صرفا تا آخرین سلول دارای مقدار محدوده را انتخاب میکند.

در موارد وجود سلول خالی میتوانید از مشخصه usedrange استفاده کنید.

انتخاب محدوده  در vba با استفاده از مشخصه usedrange

با استفاده از usedrange میتوانید

Sub SelectUsedRegion()
ActiveSheet.UsedRange.Select
End Sub

به تمام سلول هایی که دچار تغییر شده اند اشاره کنید. بنابراین کد زیر تمام سلول های استفاده شده در یک شیت فعال را انتخاب میکند.

توجه داشته باشید که در مواردی که یک سلول دور مورد استفاده قرار گرفته است نیز توسط این کد مورد توجه قرار خواهد گرفت و تمام سلول ها تا آن سلول انتخاب خواهند شد.

انتخاب محدوده با استفاده از مشخصه end

این قسمت از مطلب بسیار مفید و کاربردی خواهد بود. مشخصه End به شما امکان میدهد آخرین سلول پر را انتخاب کنید. با استفاده از این مشخصه میتوانید خاصیت ترکیب Ctrl و فلش های بالا و پایین  یا ctrl و کلیدهای راست و چپ را که احتمالا در اکسل استفاده کرده اید، استفاده کنید. مثال زیر به خوبی این موضوع را تشریح خواهد کرد:

فرض کنید مجموعه داده ای به شکل زیر را در اختیار دارید میخواهید آخرین سلول دارای مقدار این مجموعه را در ستون A انتخاب کنید.

مشکل اینجاست که این مجموعه داده دائما دچار تغییر میشود و شما دقیقا نمی دانید کدام سلول آخرین سلول است. اگر بخواهید با استفاده از کیبورد این کار را انجام دهید، می بایست ابتدا در ستون A قرار گیرید و سپس با فشار دادن کلیدهای ترکیبی ctrl و فلش پایین به آخرین سلول پر منتقل شوید.

اما از آنجایی که محدوده به صورت مستمر دچار تغییر میشود بهتر است از VBA برای انجام این کار  استفاده کرد:

Sub GoToLastFilledCell()
Range("A1").End(xlDown).Select
End Sub

کد بالا شما را به اخرین سلول دارای مقدار در ستون A منتقل میکند.

به همین شکل میتوانید به اخرین سلول دارای مقدار در یک ردف نیر منتقل شوید:

Sub GoToLastFilledCell()
Range("A1").End(xlToRight).Select
End Sub

حال اگر بخواهیم به جای پریدن به آخرین سلول، کل ستون را انتخاب کنیم، چه باید کرد؟  برای انجام این کار:

در کد بالا، سلول ابتدایی و انتهایی محدوده دلخواه را با استفاده از روش های گفته شده، مشخص کردیم و سپس محدوده بین این دو سلول را انتخاب کردیم. در این روش مهم نیست چه تعداد از سلول های محدوده پر هستند، به هر حال انتخاب خواهد شد.

Sub SelectFilledCells()
Range("A1", Range("A1").End(xlDown)).Select
End Sub

در کد ((Range(“A1”, Range(“A1”).End(xlDown، آدرس A1 اولین سلول را مشخص میکند و سپس (Range(“A1”).End(xlDown آخرین سلول مورد نظر در محدوده را برای VBA تعیین میکند. از آنجایی که ما هر دو سر محدوده را مشخص کردیم، متد select تمام سلول های دارای مقدار بین این دو را انتخاب خواهد کرد.

به همین صورت میتوانید محدوده ای را که دارای چند ردیف و ستون باشد را نیز انتخاب کنید. کد زیر تمام محدوده دارای مقدار شروع شده از سلول A1 را انتخاب میکند.

Sub SelectFilledCells()
Range("A1", Range("A1").End(xlDown).End(xlToRight)).Select
End Sub

تفاوت بین مشخصه های currentregion و end

با استفاده از مشخصه end شما میتوانید سلول شروع محدوده را مشخص کنید. برای مثال اگر داده های شما در محدوده A1:D20 قرار دارد ولی اولین ردیف عنوان ردیف است، میتوانید با استفاده از End کل محدوده را به جز ردیف عنوان انتخاب کنید:

Sub SelectFilledCells()
Range("A2", Range("A2").End(xlDown).End(xlToRight)).Select
End Sub

ولی currentregion به صورت اتوماتیک کل محدوده از جمله عنوان ها را انتخاب خواهد کرد.

تاکنون در این مطلب با روش های انتخاب سلول ها آشنا شدید حال ببینیم پس از اینکه سلول ها و محدوده را انتخاب کردیم، میتوانیم چه کارهایی انجام دهیم.

کپی کردن سلول ها و محدوده ها با استفاده از برنامه نویسی VBA

همانطور که در ابتدای این مطلب گفته شد، همیشه برای انجام کار بر روی محدوده ها نیاز به انتخاب آنها نیست. در این قسمت به صورت عملی با این موضوع آشنا خواهید شد.

کپی کردن یک سلول

اگر بخواهیم سلول A1 را کپی کنیم و در سلول D1 قرار دهیم از کد زیر استفاده میکنیم:

Sub CopyCell()
Range("A1").Copy Range("D1")
End Sub

در این روش متد copy سلول مورد نظر را کپی میکند و در مقصد مورد نظر آن را paste میکند. در این روش حتی اگر داده ای درون سلول مقصد باشد هم عملیات paste انجام خواهد شد. اگر میخواهید در صورت پر بودن سلول اکسل پیغامی برای شما ارسال کند، میتوانید از شیوه زیر استفاده کنید:

Sub CopyCell()
If Range("D1").Value <> "" Then
Response = MsgBox("Do you want to overwrite the existing data", vbYesNo)
End If
If Response = vbYes Then
Range("A1").Copy Range("D1")
End If
End Sub

کپی کردن یک محدوده با اندازه ثابت

اگر میخواهید سلول های A1:D20 را در محدوده J1:M20 کپی کنید میتوانید از کد زیر استفاده کنید:

Sub CopyRange()
Range("A1:D20").Copy Range("J1")
End Sub

برای مشخص کردن محدوده مقصد کافیست سلول بالا سمت چپ را مشخص کنید، اکسل محدوده را تشخیص خواهد داد. برای کپی کردن داده از یک شیت به یک شیت دیگر میتوانید به شکل زیر عمل کنید:

Sub CopyRange()
Range("A1:D20").Copy Worksheets("Sheet2").Range("A1")
End Sub

در صورتی که شیت مبدا شیتِ فعال نیست، می بایست نام شیت مبدا را نیز مشخص کنید:

Sub CopyRange()
Worksheets("Sheet1").Range("A1:D20").Copy Worksheets("Sheet2").Range("A1")
End Sub

کپی کردن یک محدوده متغیر با استفاده از VBA

یک راه برای کپی کردن محدوده های دارای اندازه متغیر تبدیل آنها به محدوده دارای نام یا جدول است.

ولی اگر قادر به انجام این کار نیستید، میتوانید از مشخصه های End یا currentregion محدوده مورد نظر استفاده کنید. در کد زیر با استفاده از Currentregion محدوده مورد نظر کپی میشود و در sheet2 قرار می گیرد.

Sub CopyCurrentRegion()
Range("A1").CurrentRegion.Copy Worksheets("Sheet2").Range("A1")
End Sub

اگر میخواهید اولین ستون داده ها خود تا آخرین سلول پر را کپی کنید، میتوانید از کد زیر استفاده کنید:

Sub CopyCurrentRegion()
Range("A1", Range("A1").End(xlDown)).Copy Worksheets("Sheet2").Range("A1")
End Sub

در حال تکمیل ….