Dear Mely,Apakah Msgnya seperti ini :
Kalau ya, berarti di dalam Cakupan (Range) yang mau di ClearContents ada sel
yang terprotect.
Untuk membuka proteksi selnya dapat dilakukan dengan cara membuka proteksi
sheetnya, memilih sel yang ingin dibuka proteksinya dengan jalan mencabut
centang pada kotak disamping Locked Cell (Format Cells, Protection , klik pada
kotak di sebelah kiri Locked Cells untuk menghilangkan tanda
centangnya).Kemudian lakukan proteksi Sheet lagi.
Salam,HK
From: "Melyana Silaban [email protected] [belajar-excel]"
<[email protected]>
To: [email protected]
Sent: Wednesday, 4 January 2017, 15:38
Subject: Re: [belajar-excel] Membuat perintah macro untuk sheet yg areanya
terproteksi
Pak Hendrik,Terima kasih atas responnya.Sepertinya saya masih belum paham
untuk menerapkan script yg bapak berikan.Dalam contoh cuplikan script macro
dibawah ini:
Sub Hitung()
Dim rngKlir As Range Set rngKlir =
Sheets("Sheet1").Range("F4").CurrentRegion Set rngKlir = rngKlir.Offset(1,
0).Resize(rngKlir.Rows.Count - 1) Application.ScreenUpdating = False
rngKlir.ClearContents Worksheets("Sheet1").Activate
Range("D4").Select ActiveCell.FormulaR1C1 = _
"=IF(ISERROR((R[1]C[-2]-RC[-2])+1),"""",((R[1]C[-2]-RC[-2])+1))" 'hitung
jumlah baris di kolom E mulai dari cell E4 Dim brs As Integer brs =
Range("D4").Value Range("E4:E" & brs + 3).ClearContents Range("E4:E" &
brs + 3).FormulaR1C1 = "=N(R[-1]C)+1" Cancel = True
perintah diatas sudah langsung stop pada baris berwarna kuning.ketika saya coba
rubah range (F4) pada currentRegion dgn cell nomor yg bapak berikan, hal ini
tetap tidak berjalan.Maklum, pak....masih belajar mengenal script macro, jadi
masih perlu banyak belajar.
Terima kasih sebelumnya.
Salam,Mely
On Wed, Jan 4, 2017 at 2:13 PM, hendrik karnadi [email protected]
[belajar-excel] <[email protected]> wrote:
Hai Mely,Untuk menghapus sel yang tidak terproteksi gunakan
perintah:Range("rangenya"). ClearContents.
Contoh perintahnya:Range("B2:B3,D4,E4:G1000"). ClearContents
Salam,HK
From: "Melyana Silaban [email protected] [belajar-excel]"
<[email protected] >
To: [email protected]
Sent: Friday, 23 December 2016, 14:00
Subject: [belajar-excel] Membuat perintah macro untuk sheet yg areanya
terproteksi [1 Attachment]
Dear para master excel & macro,Mohon pencerahan untuk membuat perintah
macro yg berfungsi meng-remove data dari cell tertentu seperti dalam file
terlampir ini(warna hijau yg tidak terproteksi)Dalam file ini beberapa cell
terproteksi agar tidak ada tambahan sesuatu apapun di area tsb, sedangkan are
yg tidak ter-proteksi adalah area yg akan selalu perlu di-update. Di dalam
scrip macro yg saya buat contohnya ini tidak mampu untuk me-remove data di area
yg TIDAK TER-PROTEKSI.Hal ini dikarenakan keterbatasan pengetahuan saya tentang
macro ini. Untuk itulah mohon pencerahan dari para pakar macro untuk membantu
memberikansolusi terhadap permasalahan ini.Sebelumnya saya sampaikan banyak
terima kasih atas bantuan yg diberikan.
Salam,Mely
#yiv6011076807 #yiv6011076807 -- #yiv6011076807ygrp-mkp {border:1px solid
#d8d8d8;font-family:Arial;margin:10px 0;padding:0 10px;}#yiv6011076807
#yiv6011076807ygrp-mkp hr {border:1px solid #d8d8d8;}#yiv6011076807
#yiv6011076807ygrp-mkp #yiv6011076807hd
{color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px
0;}#yiv6011076807 #yiv6011076807ygrp-mkp #yiv6011076807ads
{margin-bottom:10px;}#yiv6011076807 #yiv6011076807ygrp-mkp .yiv6011076807ad
{padding:0 0;}#yiv6011076807 #yiv6011076807ygrp-mkp .yiv6011076807ad p
{margin:0;}#yiv6011076807 #yiv6011076807ygrp-mkp .yiv6011076807ad a
{color:#0000ff;text-decoration:none;}#yiv6011076807 #yiv6011076807ygrp-sponsor
#yiv6011076807ygrp-lc {font-family:Arial;}#yiv6011076807
#yiv6011076807ygrp-sponsor #yiv6011076807ygrp-lc #yiv6011076807hd {margin:10px
0px;font-weight:700;font-size:78%;line-height:122%;}#yiv6011076807
#yiv6011076807ygrp-sponsor #yiv6011076807ygrp-lc .yiv6011076807ad
{margin-bottom:10px;padding:0 0;}#yiv6011076807 #yiv6011076807actions
{font-family:Verdana;font-size:11px;padding:10px 0;}#yiv6011076807
#yiv6011076807activity
{background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}#yiv6011076807
#yiv6011076807activity span {font-weight:700;}#yiv6011076807
#yiv6011076807activity span:first-child
{text-transform:uppercase;}#yiv6011076807 #yiv6011076807activity span a
{color:#5085b6;text-decoration:none;}#yiv6011076807 #yiv6011076807activity span
span {color:#ff7900;}#yiv6011076807 #yiv6011076807activity span
.yiv6011076807underline {text-decoration:underline;}#yiv6011076807
.yiv6011076807attach
{clear:both;display:table;font-family:Arial;font-size:12px;padding:10px
0;width:400px;}#yiv6011076807 .yiv6011076807attach div a
{text-decoration:none;}#yiv6011076807 .yiv6011076807attach img
{border:none;padding-right:5px;}#yiv6011076807 .yiv6011076807attach label
{display:block;margin-bottom:5px;}#yiv6011076807 .yiv6011076807attach label a
{text-decoration:none;}#yiv6011076807 blockquote {margin:0 0 0
4px;}#yiv6011076807 .yiv6011076807bold
{font-family:Arial;font-size:13px;font-weight:700;}#yiv6011076807
.yiv6011076807bold a {text-decoration:none;}#yiv6011076807 dd.yiv6011076807last
p a {font-family:Verdana;font-weight:700;}#yiv6011076807 dd.yiv6011076807last p
span {margin-right:10px;font-family:Verdana;font-weight:700;}#yiv6011076807
dd.yiv6011076807last p span.yiv6011076807yshortcuts
{margin-right:0;}#yiv6011076807 div.yiv6011076807attach-table div div a
{text-decoration:none;}#yiv6011076807 div.yiv6011076807attach-table
{width:400px;}#yiv6011076807 div.yiv6011076807file-title a, #yiv6011076807
div.yiv6011076807file-title a:active, #yiv6011076807
div.yiv6011076807file-title a:hover, #yiv6011076807 div.yiv6011076807file-title
a:visited {text-decoration:none;}#yiv6011076807 div.yiv6011076807photo-title a,
#yiv6011076807 div.yiv6011076807photo-title a:active, #yiv6011076807
div.yiv6011076807photo-title a:hover, #yiv6011076807
div.yiv6011076807photo-title a:visited {text-decoration:none;}#yiv6011076807
div#yiv6011076807ygrp-mlmsg #yiv6011076807ygrp-msg p a
span.yiv6011076807yshortcuts
{font-family:Verdana;font-size:10px;font-weight:normal;}#yiv6011076807
.yiv6011076807green {color:#628c2a;}#yiv6011076807 .yiv6011076807MsoNormal
{margin:0 0 0 0;}#yiv6011076807 o {font-size:0;}#yiv6011076807
#yiv6011076807photos div {float:left;width:72px;}#yiv6011076807
#yiv6011076807photos div div {border:1px solid
#666666;height:62px;overflow:hidden;width:62px;}#yiv6011076807
#yiv6011076807photos div label
{color:#666666;font-size:10px;overflow:hidden;text-align:center;white-space:nowrap;width:64px;}#yiv6011076807
#yiv6011076807reco-category {font-size:77%;}#yiv6011076807
#yiv6011076807reco-desc {font-size:77%;}#yiv6011076807 .yiv6011076807replbq
{margin:4px;}#yiv6011076807 #yiv6011076807ygrp-actbar div a:first-child
{margin-right:2px;padding-right:5px;}#yiv6011076807 #yiv6011076807ygrp-mlmsg
{font-size:13px;font-family:Arial, helvetica, clean, sans-serif;}#yiv6011076807
#yiv6011076807ygrp-mlmsg table {font-size:inherit;font:100%;}#yiv6011076807
#yiv6011076807ygrp-mlmsg select, #yiv6011076807 input, #yiv6011076807 textarea
{font:99% Arial, Helvetica, clean, sans-serif;}#yiv6011076807
#yiv6011076807ygrp-mlmsg pre, #yiv6011076807 code {font:115%
monospace;}#yiv6011076807 #yiv6011076807ygrp-mlmsg *
{line-height:1.22em;}#yiv6011076807 #yiv6011076807ygrp-mlmsg #yiv6011076807logo
{padding-bottom:10px;}#yiv6011076807 #yiv6011076807ygrp-msg p a
{font-family:Verdana;}#yiv6011076807 #yiv6011076807ygrp-msg
p#yiv6011076807attach-count span {color:#1E66AE;font-weight:700;}#yiv6011076807
#yiv6011076807ygrp-reco #yiv6011076807reco-head
{color:#ff7900;font-weight:700;}#yiv6011076807 #yiv6011076807ygrp-reco
{margin-bottom:20px;padding:0px;}#yiv6011076807 #yiv6011076807ygrp-sponsor
#yiv6011076807ov li a {font-size:130%;text-decoration:none;}#yiv6011076807
#yiv6011076807ygrp-sponsor #yiv6011076807ov li
{font-size:77%;list-style-type:square;padding:6px 0;}#yiv6011076807
#yiv6011076807ygrp-sponsor #yiv6011076807ov ul {margin:0;padding:0 0 0
8px;}#yiv6011076807 #yiv6011076807ygrp-text
{font-family:Georgia;}#yiv6011076807 #yiv6011076807ygrp-text p {margin:0 0 1em
0;}#yiv6011076807 #yiv6011076807ygrp-text tt {font-size:120%;}#yiv6011076807
#yiv6011076807ygrp-vital ul li:last-child {border-right:none
!important;}#yiv6011076807
